Excel Dates

[nowretired]

I have a spreadsheet I use for advance banking/budgeting, what my bank account will be like in a year's time.
I have dates in column A and £ in column B

A B
1 Feb 2019 £100
3 Feb 2019 £200
8 Feb 2019 £100
15 Feb 2019 £100
22 Feb 2019 £100
22 Feb 2019 £150

How do I repeat these dates down column A to

31 Jan 2020 £100
2 Feb 2020 £200
7 Feb 2020 £100
14 Feb 2020 £100
21 Feb 2020 £100
22 Feb 2020 £150
29 Feb 2020 £100

given that the 1 Feb 2019 date is every 7 days the 3 Feb 2019 is every 28 days and the 22 Feb 2019 date is every month?

There are of course many more dates than the 3 different amounts I have illustrated, in fact there are sometimes 20 dates in one month.

I can of course do it if all the dates have the same interval between them.

[nowretired]

And of course my formatting has gone to Hell.

A is dates and B is £

[Slarti]

Simple maths in A3 =A1+7 Assuming your sample data starts at A1.

With 20 regular transactions you will need to create formulas down to the 21st line to get it so that when you copy rows 2 to 21 and paste them into row 22, everything will make sense.

When you have copied down enough times to reach 29 Feb 2020, then copy all of the formulas and paste them over themselves with Paste Special Values, otherwise if you change something you could get odd results.

Probably best to format the whole column to your preferred date format first.

Slarti

[Itsallaguess]

I have a spreadsheet I use for advance banking/budgeting, what my bank account will be like in a year's time.
I have dates in column A and £ in column B

A B
1 Feb 2019 £100
3 Feb 2019 £200
8 Feb 2019 £100
15 Feb 2019 £100
22 Feb 2019 £100
22 Feb 2019 £150

How do I repeat these dates down column A to

31 Jan 2020 £100
2 Feb 2020 £200
7 Feb 2020 £100
14 Feb 2020 £100
21 Feb 2020 £100
22 Feb 2020 £150
29 Feb 2020 £100

It's not entirely clear if I've understood your requirement correctly, but in case it helps and you're not already aware, Excel will enable you to do this -

In Cell A1 enter -

1/2/2019

then, in Cell A2 enter -

=A1+2

You'll see that Excel will then actually show '03/02/2019' in Cell A2, which is of course the date in Cell A1 with an additional 2 days.

You can then format those cells so that they show the dates in the '01 February 2019' format, whilst still maintaining your date manipulations.

I'm not entirely convinced that helps, but given you've not specifically said that you're aware of the above, I just thought I'd mention it...

Cheers,

Itsallaguess

[staffordian]

Another way...

Enter the first four or five dates in the column.

Then highlight these by dragging the mouse over them.

You should have a box around them with little black squares in the corner.

Click and hold the bottom right square and drag down to cover as many cells as you want to fill - 53 or so.

You should have the pattern you first entered maintained to the last cell you've dragged to.

Delete any at the bottom of the column if you've done too many.

[nowretired]

Click and hold the bottom right square and drag down to cover as many cells as you want to fill - 53 or so.

You should have the pattern you first entered maintained to the last cell you've dragged to.

That just repeats the same dates.

With 20 regular transactions you will need to create formulas down to the 21st line to get it so that when you copy rows 2 to 21 and paste them into row 22, everything will make sense.

It doesn't make sense to me.

That is what I was trying to avoid, writing formulas for over 400 lines.

[staffordian]

Sorry, yes I had wrongly assumed there was a regular gap between the dates.

If the gaps are random, I can't see any way.

[Stompa]

Given that the periods are so variable (7 days, 28 days, monthly, and perhaps others?), I don't see how it can be done as a repeating block.

I'd be inclined to do all the 7 day ones first:

1 Feb 2019 £100
=A1+7
etc.

then the 28 day ones:
<start date> £200
=A<start row>+28
etc.

then the monthly ones
<start date> £150
=EDATE(A<start row>,1)
etc.

[Though note that the monthly formula will fail if it encounters invalid dates e.g. 31 Apr]

Then copy and paste special values, to turn all the date formulae into values. Then sort the whole lot by date to get them in the desired order.

[Alaric]

I can of course do it if all the dates have the same interval between them.

Create all the dates for a year with the "+1" formula, freeze the column with Copy/Paste Special Values. Delete all the rows you don't want.

Or even leave every date there and only fill in the amounts column when needed.

[Breelander]

...given that the 1 Feb 2019 date is every 7 days the 3 Feb 2019 is every 28 days and the 22 Feb 2019 date is every month?

No need for formulas, you can still use autofill if you go about it the right way.

The click and drag down to fill option will not work with the dates mixed like that, but will work individually for each set of 7-day, 28-day or one-month dates. So do each separately...


type the first few...

01 February 2019 £100
08 February 2019 £100
15 February 2019 £100

...then click and drag down to fill up to 2020.

Repeat below that for the '28-days'

03 February 2019 £200
03 March 2019 £200
31 March 2019 £200

Then below that for the monthly ones.

22 February 2019 £150
22 March 2019 £150
22 April 2019 £150


Then just select the whole lot and sort by the date to get them in the right order. I got....

03 January 2020 £100
05 January 2020 £200
10 January 2020 £100
17 January 2020 £100
22 January 2020 £150
24 January 2020 £100
31 January 2020 £100
02 February 2020 £200
07 February 2020 £100
14 February 2020 £100
22 February 2020 £150

[modellingman]

Several posters have commented on the difficulty of combining the different intervals (7 days, 28 days, monthly, etc) between the transactions. Breelander's and stompa's solutions acknowledge this difficulty by separating out the different intervals, separately generating the transaction dates associated with each interval and then combining these back together in a single list using a sort operation. All very sensible.

However, there is a way to do all this formulaically.

The approach involves formally defining transaction types. The OP's data has 3 transaction types. These are
- a payment of £100, starting 1 Feb 2019, repeating every 7th day
- a payment of £200, starting 3 Feb 2019, repeating every 28th day
- a payment of £150, starting 22 Feb 2019, repeating on the 22nd day of every month
So a transaction type has a payment amount, start date and repeat pattern.

The approach involves first constructing a table whose rows correspond to the days of the future period the OP is interested in. For example if this period is 1 Feb 2019 to 29 Feb 2020, their would be 365+29=394 days in the period, so 394 rows in the body of the table. The columns correspond to the transaction types, so 3 columns in the body of the table for the OP's example. The entries in the cells of the table are True/False according to whether the date of the cell's row will be the date of a transaction of the type corresponding to the cell's column. Each row has an associated date, say date, and each column is associated with the starting date, say startdate, of its transaction type. Formulas can be constructed to generate the values in the table's cells based on each cell's date and startdate values. The formula are tailored to the transaction type's repeating pattern. For example the formula in a cell of a column where the transaction type has a 7 day repeating pattern would be =MOD(date-startdate,7)=0. The complete table should also contain row headers with the date values and column headers with the startdate values, as these will allow construction of the cell formulas. If appropriate mixed relative/absolute referencing of the cells containing the header values is used in the cell formulas, then it will be possible to copy formulas in the first row of the table to the remaining rows.

Once constructed, the above table actually contains all the information necessary to construct a sequential list of transaction dates in the period of interest. The list will have repeated dates if more than one transaction occurs on a date and will not include dates without transactions. How this list is constructed is sketched out below.

First, the total number of Trues in the table tells us how many transactions there are in the period of interest so, we can start a list of these transactions by sequentially numbering them starting at 1. Second, by adding up the number of Trues in each row and accumulating this down the rows, we can identify those the row numbers of the table which correspond to transaction dates in the period of interest and the number of transactions on such a date. This allows the addition of a transaction date to each transaction in the nascent sequential list. Further refinements are possible that allow the addition of the transaction type and hence payment amount. Grasp of functions such as =MATCH() and =VLOOKUP() is essential for constructing the transaction list.

I have glossed over a lot of detail but the overall message is that it is possible to deal with the OP's problem in a formulaic way. Whether it is worth the effort of doing so is another matter completely!

[dionaeamuscipula]

I've not checked it but you could probably set up a calendar in Google, outlook, or something, using the reasonably sophisticated recurrence functions they have, and then export the whole thing into Excel using a CSV file.

DM

[modellingman]

Its been a slow Sunday here, so here is an Excel spreadsheet implementing my earlier post.

https://www.mediafire.com/file/o73c9zfd ... .xlsx/file

To the OP: I think there's a slight error in your opening post. The spreadsheet tells me the final £100 transaction you list should have a date of 28/02/2020 rather than 29/02/2020 as you have stated.

[Slarti]

With 20 regular transactions you will need to create formulas down to the 21st line to get it so that when you copy rows 2 to 21 and paste them into row 22, everything will make sense.

It doesn't make sense to me.

That is what I was trying to avoid, writing formulas for over 400 lines.

If you only create 19 formulas, when you copy and paste the first block you will have a value on line 21 instead of a formula, so lines 21 to 40 would be exactly the same as lines 1 to 20.

Slarti

[forlesen]

Yet another option not mentioned so far is to use a pivot table to consolidate multiple separate blocks of data.

As already suggested in earlier posts, create separate blocks of data for the 7 day interval dates, monthly dates, 4-weekly dates, etc. These blocks can be created with series fill, formulae, manual entry, or any combination as you prefer. The blocks can be laid out in separate columns of one worksheet, in multiple worksheets, even different workbooks.

These blocks can then be consolidated into a single pivot table using the PivotTable and PivotChart Wizard (NB: this is one tool, not two separate tools!), as described here:

https://support.office.com/en-us/articl ... fc8adeeeb5

I tried the recipe titled: "Consolidate data without using page fields", and it worked fine.

This approach gives you access to all the usual pivot table features, such as the potentially useful property of automatically adding the £ figures if different blocks of data have the same date in them. That is, if 3/9/2019 appears both in your 7 day interval block (e.g. with £100) and your 3rd day of the month block (e.g. with £50), the pivot table will by default have a single row for this date, containing the value £150.