# Calculate next date

#### Mindlesh

##### Board Regular
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

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.

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?

That's perfect, thank you!

Replies
1
Views
411
Replies
7
Views
235
Replies
3
Views
219
Replies
7
Views
157
Replies
1
Views
162

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?

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