Date formula for leap year

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)))

Answers

  • Toufong Vang
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!