# Date formula based on frequency

Options
✭✭✭

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

• ✭✭✭
Options

Thank you, Paul! This worked perfectly!!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

Thank you, Paul! This worked perfectly!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• edited 09/20/23
Options

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

• ✭✭✭✭✭✭
Options

Will you ever need more than 12 months?

• Options

I would not, the maximum would be 12

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!