Calculate next future date

[Mindlesh]

Given:

=IF([@Start]>TODAY(),[@Start],DATE(YEAR([@Start]),MONTH([@Start])+[@Frequency],DAY([@Start])))

If Start=31/07/2016 and Frequency=12, the result is 31/07/2017.
If Start=31/01/2016 and Frequency=12, the result is 31/01/2017. How can I adapt the formula so that the result is 31/01/2018 (the next date on or after today)?

 

[Mindlesh]

Far from being descriptive.

If Start=31/01/2015 and Frequency=12, the result is 31/01/2017 instead of 31/01/2018; ie. not the next date on or after today.

 

[snjpverma]

What would be the result here if the frequency is 3 instead of 12

 

[Mindlesh]

What would be the result here if the frequency is 3 instead of 12

If Start=31/01/2016 or Start=31/01/2015, and Frequency=3, the result should be 1/05/2017. (Ideally, it would be 30/04/2017, but that would be more difficult I guess.)

 

[Tetra201]

If Start=31/01/2015 and Frequency=12, the result is 31/01/2017 instead of 31/01/2018; ie. not the next date on or after today.

If Start=31/01/2016 or Start=31/01/2015, and Frequency=3, the result should be 1/05/2017. (Ideally, it would be 30/04/2017, but that would be more difficult I guess.)

This gives the desired results:

=IF([@Start]>TODAY(),[@Start],EDATE([@Start],[@Frequency]*IFERROR(2+INT(DATEDIF(EDATE([@Start],[@Frequency]),TODAY(),"m")/[@Frequency]),1)))

 

[Mindlesh]

Fantastic, thank you! Just a couple of points:

1. If the next date is today, it is skipped for the next date after today.
2. I don't suppose you could adapt the formula for the case where Frequency is expressed in days, please?

 

[snjpverma]

I have assumed that the Start date is in C2 and the frequency is in cell D2. You can adjust the ranges as per your suitability.

This shorter version worked for me.


If the frequency is in months use this

=IF(C2>TODAY(),C2,DATE(YEAR(C2)+1,MONTH(C2)+D2,DAY(C2))

If the frequency is in days use this

=IF(C2>TODAY(),C2,DATE(YEAR(C2)+1,MONTH(C2)+(D2/30.5),DAY(C2))

 

[Mindlesh]

=IF(C2>TODAY(),C2,DATE(YEAR(C2)+1,MONTH(C2)+D2,DAY(C2))

This produces very different results to Tetra201's formula.

=IF(C2>TODAY(),C2,DATE(YEAR(C2)+1,MONTH(C2)+(D2/30.5),DAY(C2))

This adds a year to each Start date.