Solved: How to add a year to my date - Page 2

analorios · 12-04-2019 02:25 AM

Hello Community,

I am trying to add a year to a date and I don’t find the right function for doing this. ¿Could anyone help me?

Suvrutt_Gurjar

Please explore if following expressions help

An expression something like below will give exact same day next year (Input 12/24/2019 will give 12/24/2020 as output)

IF(OR(AND(MOD(YEAR([DateColumn]), 4)=0,MONTH([DateColumn])<3),AND(MOD(YEAR([DateColumn]), 4)=3,MONTH([DateColumn])>2)), [DateColumn]+366,IF(AND(MOD(YEAR([DateColumn]), 4)=0,DAY([DateColumn])=29),[DateColumn]+364, [DateColumn]+365))

An expression something like below will give just the preevious day next year (Input 12/24/2019 will give 12/23/2020 as output)

IF(OR(AND(MOD(YEAR([DateColumn]), 4)=0,MONTH([DateColumn])<3),AND(MOD(YEAR([DateColumn]), 4)=3,MONTH([DateColumn])>2)), [DateColumn]+365,IF(AND(MOD(YEAR([DateColumn]), 4)=0,DAY([DateColumn])=29),[DateColumn]+363, [DateColumn]+364))

I tested it for border cases like 28 Feb, 29 Feb, 1st March, leap , no leap year scenarios as shiwn below but the expressions could still be tested more, especially if there are some critical calculations based on the date.

In the testing sample below, [RepairDate] is input and NextYear and NextYear2 are outputs based on the two expressions mentioned above.

View solution in original post