Creating date formula to move a cell on a specific period in time based on drop down options

I have a sheet of regular tasks that are either weekly, monthly, or annually in frequency.

I am trying to create a formula that will automatically create the next date based off the last scheduled date plus the frequency. For annually I am using the statement below:

=IF(Frequency@row = "Annually", DATE(YEAR([Last Scheduled Date]@row) + 1, MONTH([Last Scheduled Date]@row), DAY([Last Scheduled Date]@row)))

For monthly I am using the statement below:

=IF(Frequency@row = "Monthly", DATE(YEAR([Last Scheduled Date]@row), MONTH([Last Scheduled Date]@row) + 1, DAY([Last Scheduled Date]@row)))

For weekly I am using the statement below:

=IF(Frequency@row = "Weekly", DATE(YEAR([Last Scheduled Date]@row), MONTH([Last Scheduled Date]@row), DAY([Last Scheduled Date]@row + 7)))

The annual statement is great but the weekly and monthly statements only work until the date moves from December to January at which point it returns the incorrect date.

I have also only managed to get these formulas to work separately. As soon as I try to combine it doesn't work.

Any help would in combining the statements and fixing the December to January issue be much appreciated.

«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!