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.

Find more posts tagged with

Accepted answers

All comments

Colette Hughes

Perfect. You're a legend!

Paul Newcome

Happy to help. 👍️

Pro tip:

The WORKDAY function can also accommodate a range of holidays. I generally put them on another sheet going down a column and then just reference the entire column as a {Cross Sheet Reference} in the holiday section of the function.

https://help.smartsheet.com/function/workday

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.