Date formula based on frequency
Hi SS Community -
Trying to navigate a formula that will populate a date based on a date and frequency column. Example - when is the next time a document needs to be reviewed (in date format), based on the last date of review and the frequency required (quarterly or annually).
Best Answers
-
Annually is the less complicated of the two.
=DATE(YEAR([Last Review Date]@row) + 1, MONTH([Last Review Date]@row), DAY([Last Review Date]@row))
Next we need to work through the quarterly.
=IFERROR(DATE(YEAR([Last Review Date]@row), MONTH([Last Review Date]@row) + 3, DAY([Last Review Date]@row)), DATE(YEAR([Last Review Date]@row) + 1, MONTH([Last Review Date]@row) - 9, DAY([Last Review Date]@row)))
Then we drop that into an IF statement to say that if the frequency is "Annually" then output the result of the annual formula, otherwise output the result of the quarterly formula.
=IF([Frequency of Review]@row = "Annually", annual_formula, quarter_formula)
And this should do the trick for you...
=IF([Frequency of Review]@row = "Annually", DATE(YEAR([Last Review Date]@row) + 1, MONTH([Last Review Date]@row), DAY([Last Review Date]@row)), IFERROR(DATE(YEAR([Last Review Date]@row), MONTH([Last Review Date]@row) + 3, DAY([Last Review Date]@row)), DATE(YEAR([Last Review Date]@row) + 1, MONTH([Last Review Date]@row) - 9, DAY([Last Review Date]@row))))
-
Thank you, Paul! This worked perfectly!!
Answers
-
Annually is the less complicated of the two.
=DATE(YEAR([Last Review Date]@row) + 1, MONTH([Last Review Date]@row), DAY([Last Review Date]@row))
Next we need to work through the quarterly.
=IFERROR(DATE(YEAR([Last Review Date]@row), MONTH([Last Review Date]@row) + 3, DAY([Last Review Date]@row)), DATE(YEAR([Last Review Date]@row) + 1, MONTH([Last Review Date]@row) - 9, DAY([Last Review Date]@row)))
Then we drop that into an IF statement to say that if the frequency is "Annually" then output the result of the annual formula, otherwise output the result of the quarterly formula.
=IF([Frequency of Review]@row = "Annually", annual_formula, quarter_formula)
And this should do the trick for you...
=IF([Frequency of Review]@row = "Annually", DATE(YEAR([Last Review Date]@row) + 1, MONTH([Last Review Date]@row), DAY([Last Review Date]@row)), IFERROR(DATE(YEAR([Last Review Date]@row), MONTH([Last Review Date]@row) + 3, DAY([Last Review Date]@row)), DATE(YEAR([Last Review Date]@row) + 1, MONTH([Last Review Date]@row) - 9, DAY([Last Review Date]@row))))
-
Thank you, Paul! This worked perfectly!!
-
Happy to help. 👍️
-
Thank you for this my man, is it possible for you to detail what this would look like if my frequency is based on the number of months versus the word "Annually" ?
My Main issue is that when I use =DATE(YEAR([Last Maintenance/Inspection]@row), MONTH([Last Maintenance/Inspection]@row) + [Frequency (Month)]@row, DAY([Last Maintenance/Inspection]@row))
I run into an issue where if the month exceeds 12 it will throw an error when adding it from the frequency. Thanks
For example: Here's how my sheet is currently
-
Will you ever need more than 12 months?
-
I would not, the maximum would be 12
-
Give this a try...
=IFERROR(DATE(YEAR([Last Maintenance/Inspection]@row), MONTH([Last Maintenance/Inspection]@row) + [Frequency (Month)]@row, DAY([Last Maintenance/Inspection]@row)), DATE(YEAR([Last Maintenance/Inspection]@row), MONTH([Last Maintenance/Inspection]@row) - (12 - [Frequency (Month)]@row), DAY([Last Maintenance/Inspection]@row)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!