Add year to date

[Stanman]

Hi,

I want to add a year to the current date taking account of leap year but its not returning what I want.
My staritng date is 06/04/2020 and I would like to add a year / 52/53 weeks to this. I am using the formula =DATE(YEAR(I6)+1, MONTH(I6), DAY(I6)) and this returns 06/04/2021 rasther than 05/04/2021 that I was expecting.

Any help?

 

[mole999]

if it has to be that way guess you need to add an IF cluse, check its before the 29th Feb and remove a day, greater than and it should be normal.

Is it necessary rather than not what you were expecting

 

[GlennUK]

Do you want to add 52 weeks exactly? If so, then do =I6+(52*7)

 

[Stanman]

Thank you @GlennUK worked a charm

 

[Stanman]

@glenuk, that formula gave what I was expecting exactly a year later but how will this work for leap years?

 

[FormR]

Hi, here is an alternative that handles leap years.

=EDATE(I6,12)

 

[Stanman]

Thank you for that @FormR , is there anyway to default that to the next Monday that follows the date, using 01/04/2019 as the start date, returns 01/04/2020. I was expecting 06/04/2020

 

[FormR]

is there anyway to default that to the next Monday that follows the date

Hi, yes - you could try:

=WORKDAY.INTL(EDATE(I6,12)-1,1,"0111111")