Calculate next future date

M

Mindlesh

Board Regular
Joined
Apr 2, 2014
Messages
172
Given:
Code: 
=IF([@Start]>TODAY(),[@Start],DATE(YEAR([@Start]),MONTH([@Start])+[@Frequency],DAY([@Start])))
If Start=31/07/2016 and Frequency=12, the result is 31/07/2017.
If Start=31/01/2016 and Frequency=12, the result is 31/01/2017. How can I adapt the formula so that the result is 31/01/2018 (the next date on or after today)?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Are you adding 12 months because the frequency is 12?
Or by default 12 months have to be added?
 
Upvote 0
Check if this works

=IF([@Start]>TODAY(),([@Start],DATE(YEAR([@Start])+1,MONTH([@Start])+[@Frequency],DAY([@Start])))
 
Upvote 0
@Mindlesh:

See if this works for you:

=IF([@Start]>TODAY(),[@Start],EDATE([@Start],[@Frequency]*(1+(TODAY()>=EDATE([@Start],[@Frequency])))))
 
Last edited:
Upvote 0
snjpverma said:
=IF([@Start]>TODAY(),([@Start],DATE(YEAR([@Start])+1,MONTH([@Start])+[@Frequency],DAY([@Start])))
Click to expand...

No: that will always add 1 year to Start; if Start were in 2015, then the result would be in 2017 instead of 2018.
 
Last edited:
Upvote 0
Tetra201 said:
=IF([@Start]>TODAY(),[@Start],EDATE([@Start],[@Frequency]*(1+(TODAY()>=EDATE([@Start],[@Frequency])))))
Click to expand...

This also always adds 1 year to Start; if Start were in 2015, then the result would be in 2017 instead of 2018.
 
Upvote 0
Try this

=IF([@Start]>TODAY(),[@Start],DATE(YEAR(2018),MONTH([@Start])+[@Frequency],DAY([@Start])))
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Count the number of days between two dates that are in 2024
2
Replies
15
Views
255
Fluff
Fluff
S
  • Question
Returning 1 or 0 based on multiple dates
Replies
2
Views
67
bebo021999
B
U
  • Question
Week number based on the year starting with a particular date
Replies
2
Views
343
Jeffrey Mahoney
Jeffrey Mahoney
A
  • Question
Insert new column(DATE) up to finish month and move to next sheet
Replies
0
Views
121
Amer Omar
A
P
  • Question
Analytics Project - Sumifs
Replies
1
Views
220
bobaol
B

Forum statistics

Threads
1,214,885
Messages
6,122,085
Members
449,064
Latest member
MattDRT

Share this page

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