Date formula for leap year

Emi

Hello All,

I am hoping to get some help for the date formulation.

My date of manufacture is 04/01/2019 and I want to use 04/01/2019 to add 3 years and minus 90 days from 3 years. There is a leap year in 2020. I was hoping I can get 01/02/2022 (because of the one extra day in 2020); however, it kept populate 01/01/2022. Is there another way to do this?

This is the current formula that I am using

=DATE(YEAR([Retest Date / Action Due Date]365) + 3, MONTH([Retest Date / Action Due Date]365) - 3, DAY(([Retest Date / Action Due Date]365)))

Toufong Vang

Hi, @Emi ,

Assuming that 04/01/2019 is in the column, Date of Mfg...

(1) Use NETDAYS() to find the number of days between 04/01/2019 and 3 years from 04/01/2019--which is 04/01/2022. The date 3 years from any date in Date of Mfg is DATE( YEAR([Date of Mfg]@row) + 3 , MONTH([Date of Mfg]@row) , DAY([Date of Mfg]@row) )

(2) Subtract 90 days from that total.

(3) Add the remaining to 04/01/2019.

(4) The result is 01/02/2022.

= [Date of Mfg]@row + NETDAYS([Date of Mfg]@row, DATE( YEAR([Date of Mfg]@row)+ 3 , MONTH([Date of Mfg]@row) , DAY([Date of Mfg]@row)) ) - 90