How to change date by 5 years with a formula?

[Excelllllllllllllllllllll]

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.

 

[jasonb75]

It looks as if part of your question is missing.

Have you looked at the EDATE() function?

 

[Fluff]

When using < or > symbols please add a space either side.
How about
=EDATE(A1,60*ROUNDUP((YEAR(TODAY())-YEAR(A1))/5,0))

 

[Excelllllllllllllllllllll]

Adapting this to my needed date I have

=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?

 

[jasonb75]

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.

 

[Fluff]

I just changed my system clock to 2040 & it showed a date of 27/11/2042

 

[Excelllllllllllllllllllll]

I just changed my system clock to 2040 & it showed a date of 27/11/2042

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

 

[jasonb75]

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.