Shifting Time Series Data forward in time

Copper Contributor

I have an excel table that calculates monthly cashflows over time.  I want to duplicate it, slide everything down 1 month, and repeat this process for a total of 60 months, then be able to have a grand total out to the right of the 60 individual tables.  Is there a quicker/easier way to do this than manually copying the table and moving everything down by 1 month, having to do this 60 times?

Thanks,

Mark. 

2 Replies

Hi @MarkS9198 

 

To shift the time one month you can add 1 as in this formula:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A13))

Assuming that your date in cell A1

@MarkS9198  wrote:  ``Is there a quicker/easier way to do this``

 

Difficult to say without seeing the Excel file.  In the future, attach an example Excel file.

 

If your first 60 dates are in A3:A62, enter =EDATE(A3,6) into A63, copy A63 and paste into A64:A122.

 

If you want to copy the corresponding cash flow values in B3:B62, which might have formulas, enter =B2 into B63, copy B3 and paste into B64:B122.

 

If you want to copy the constant values or formulas in B3:B62, copy B3:B62 and paste into B63:B122.