Calculate next date

Mindlesh

Board Regular
Joined
Apr 2, 2014
Messages
172
I have a table with columns Start, Frequency (in weeks) and Next. Next calculates the next date after today1 given Start and Frequency. (If Next is today, then today is Next).
Code:
=TODAY()-1+([@[Frequency]]*7-MOD((TODAY()-1-[@Start]),[@[Frequency]]*7))
How can I adapt Frequency and Next to accommodate months, too?


1 Not the next date after Start.
 
Last edited:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
does this work for you?

Code:
=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
 
Upvote 0
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.
 
Upvote 0
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.

Code:
=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?
 
Upvote 0

Forum statistics

Threads
1,211,772
Messages
6,103,881
Members
447,883
Latest member
bennazi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top