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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

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