Calculate next date

[Mindlesh]

I have a table with columns Start, Frequency (in weeks) and Next. Next calculates the next date after today¹ given Start and Frequency. (If Next is today, then today is Next).

=TODAY()-1+([@[Frequency]]*7-MOD((TODAY()-1-[@Start]),[@[Frequency]]*7))

How can I adapt Frequency and Next to accommodate months, too?


¹ Not the next date after Start.

 

[friel300]

does this work for you?

=IF(A1>TODAY(),A1,IF(TODAY()>DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)),DATE(YEAR(A1),MONTH(A1)+B1+1,DAY(A1)),DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))))

A = Start
B = Freq (in Months)
C = Next Date

You said months too but i assumed you ment insted, apologies if not. The above will do the same function as the one you have above only for months instead.

Hope this helps

 

[Mindlesh]

Thank you. However, I was hoping that I could specify whether the Frequency is in weeks or months, and then calculate Next based on that.

 

[friel300]

Ahh ok, Not sure how you want to switch between but the below does it by putting a M or a W in col C.

=IF(C1="W",TODAY()-1+(B1*7-MOD((TODAY()-1-A1),B1*7)),IF(C1="M",IF(A1>TODAY(),A1,IF(TODAY()>DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)),DATE(YEAR(A1),MONTH(A1)+B1+1,DAY(A1)),DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)))),"Error"))

Does that work for you?

 

[Mindlesh]

That's perfect, thank you!