Date formula based on frequency

Amber Benge · 08/20/21

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

Paul Newcome · 08/20/21

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

Amber Benge · 08/24/21

Thank you, Paul! This worked perfectly!!

Paul Newcome · 08/20/21

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

Amber Benge · 08/24/21

Thank you, Paul! This worked perfectly!!

Paul Newcome · 08/24/21

Happy to help. 👍️

andrewmezzo · 09/20/23

@Paul Newcome

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

Paul Newcome · 09/21/23

Will you ever need more than 12 months?

andrewmezzo · 09/21/23

https://community.smartsheet.com/discussion/comment/396488#Comment_396488

I would not, the maximum would be 12

Paul Newcome · 09/26/23

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

Date formula based on frequency

Best Answers

Answers

Help Article Resources

Categories