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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!