How to change date by 5 years with a formula?

Joined
Jun 13, 2017
Messages
108
Hello, I'm trying to make an excel sheet that tracks my bill payment dates.

I need a formula that adds 5 years to the date 27/11/2012 and if that's < today() then add another 5 years and if that's < today() etc. etc.

Right now I have 10 IFs just manually adding 5 years to each nested IF statement once it passes TODAY(), but I feel like a caveman doing that.

Please help.
 
Last edited by a moderator:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
It looks as if part of your question is missing.

Have you looked at the EDATE() function?
 
Upvote 0
When using < or > symbols please add a space either side.
How about
=EDATE(A1,60*ROUNDUP((YEAR(TODAY())-YEAR(A1))/5,0))
 
Upvote 0
Adapting this to my needed date I have
Code:
=EDATE("27/11/2012",60*ROUNDUP((YEAR(TODAY())-2012)/5,0))
However this only works for 2 iterations of 5 years and then stops working.
So when testing in offline mode by adjusting my calendar date manually this stops working 10 years from the date 27/11/2012.

How do I get around this?
 
Upvote 0
Now that Fluff has repaired your post I can see what is required.

Something like
=DATE(CEILING(YEAR(EDATE(TODAY()+35,36)),5)-3,11,27)
Should do it.

Breaking it down, add 35 days (from 27/11 to 01/01 of the following year), add 3 years (36 months), round year up to nearest factor of 5, then deduct the 3 years added previously.
 
Upvote 0
I just changed my system clock to 2040 & it showed a date of 27/11/2042
 
Upvote 0
I just changed my system clock to 2040 & it showed a date of 27/11/2042
nope.JPG
p.jpeg


Jasonb75's formula works with everything I tested, although I still don't fully understand how with that ceiling that I've never used and need to google now xD
Thank you both for the help and than you Fluff for the post edit - lesson learned.

Last question - in Jason's formula isn't it unsafe to just assume it'll always be 35 days to 01/01?
Wouldn't it make more sense to use something like "EDATE(DATE(YEAR(TODAY()),11,27)-DAY(27)+1,2)" which ensures it lands on 01/01 no matter how many days there are in those 2 months between the 27/11 and 01/01?
I'm just not sure how to make that work with the formula since I keep getting NUMBER and VALUE the way I'm trying to integrate it xD
 
Upvote 0
35 days is not an assumption, but a known fact, the number of days in the months involved never changes. I tried to keep the formula as simple as possible based on the information provided.

For a more generic method that would work with dates in Jan or Feb when leap years have potential to cause an error, try
=DATE(CEILING(YEAR(EDATE("1/1/"&YEAR(TODAY())+(TODAY()>=DATE(YEAR(TODAY()),11,27)),36)),5)-3,11,27)

Something that I forgot to point out earlier, if you were wondering where 36 months / 3 years comes from for the adjustments, the ceiling of 2012 at 5 year intervals is 2015, a difference of 3 years / 36 months.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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