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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

friel300

Board Regular
Joined
Jan 22, 2008
Messages
69
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
 

Mindlesh

Board Regular
Joined
Apr 2, 2014
Messages
172
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

Board Regular
Joined
Jan 22, 2008
Messages
69
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,530
Messages
5,837,904
Members
430,520
Latest member
VenkateshRajaganesan

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
Top