Formula to add 3 years to a date but act differently on Feb 29th.

[MetzTeco]

I'm looking to create a formula that will add 3 years to any date entered but will act differently if the date entered is 29th February on a leap year.

At the moment =DATE(YEAR(A1)+3,MONTH(A1),DAY(A1)) works fine for all dates apart from 29th February. If used this formula on 29/02/2020 it will return 29/02/2023 which wont exist. Instead whenever a date that entered that starts with 29/02/XXXX, i want it to instead display 28/02/XXXXX. So for 29/02/2020 it will return 28/02/2020.

Anyone know if this can be done with a formula without having to use vba code?

Any help would be appreciated.

 

[Joe4]

Welcome to the Board!

Try this instead:

=EDATE(A1,36)

See here for details on the EDATE function: MS Excel: How to use the EDATE Function (WS).

 

[MetzTeco]

Perfect! thanks a million for the quick reply.

 

[Joe4]

You are welcome.