# Some help to find the right formula - advancing dates

Copper Contributor

# Some help to find the right formula - advancing dates

I want to make a file with an advancing timeframe (period). Macros are not allowed at work, so i have to try it with formulas.

The first table is the one i want to keep - the 2nd to 4th how the cells should behave.

D2 = current date, this will be used as a reference if the period is still valid (or needs to advance one year)

D3 = first delivery ever - this date will remain fixed forever, but gives me the possibility to generate a start date for the period (D4)

D5 = end periode is generated through formula: EDATE(D4;12)-1

The 2nd to 4th are tables how i want this table to behave, based on the current date.

I've tried several functions, but none of them give me what is need. I've tried DATEDIF(D3;D2;"m") and combine it with NOW() function, but the problem is that - as long as the current date is not the same month. However if the date is in the same month and the day is somewhere between the 1st of that month the current date - this formula doesn't work anymore. Instead of adding 12 month, he add 11 months - so the periods reduce, instead of advancing exactly one year.

If anyone can help me with this, i'd appreciate it very much.

# Re: Some help to find the right formula - advancing dates

It seems that you have multiple challenges here. But I may have an incorrect understanding, so are the following all true?

1. You want to copy a blue-and-green block of eight cells to the bottom of the data when some real-world event is to be recorded.
2. You want the "Current date" formula to contain the date (and also time, if you are planning to use the NOW function) when the copy is made. And you want that value, once calculated, to be unchanged from that time forward. FormulaCD
3. You want the "Start of first delivery" row to contain the same date as the original (copy-from) row.
4. You want the "Start periode" formula to calculate either: 1) the same date as the prior "Start periode" row, if the "Current date" value is not a later date than the "Einde periode" value in the prior block of data; otherwise, 2) one year later than that prior-block date. FormulaSP
5. You want the "End periode" formula to calculate a date that is one year later than the just-calculated "Start periode" date, minus one day. FormulaEP

If your worksheet is structured with a fixed number of rows between blocks (1 empty row => 5 rows per block), then you can use a FormulaSP like this one for cell D9:

``=IF( FLOOR.MATH(D7) <= D5, D4, D5+1 )``

(Spaces are included for readability only. My delimiter between parameters is a comma; yours is a semicolon.)

FormulaCD will involve iterative calculation. Your FormulaEP (cell D5) appears to be valid.

Experiment with Sheet2 in the attached workbook.