Based on frequency selected change the formula for the created date.

I feel like this should be super simple but I am just not getting it right.

Based on Frequency: Daily, weekly, monthly I want to change the next due date

This will be calulated from Created column and +1, 7, 30 etc added. It works single instance =IF(Frequency@row = "Daily", Created@row + 1) but as soon as I try to write it for various Frequencies it no longer works.

=IF(Frequency@row = "Weekly", Created@row + 7), IF(Frequency@row = "Daily", Created@row +1)

Not sure if I am over simplifying it or just missing a key step.

Find more posts tagged with

Sheets

Formulas

functionality

Accepted answers

All comments

SoS | Dan Palenchar

Hey @vanstadenster ,

The system "Created" column does not act like a date, you need to extract it with =DATEONLY(). You can do this both within a formula (method #1) or by pulling it into a separate column and referencing that (method #2).

Method #1

you could use this formula:

=IF(Frequency@row = "Weekly", DATEONLY(Created@row) + 7), IF(Frequency@row = "Daily", DATEONLY(Created)@row +1)

Method #2

You could also pull out the Created Date into a separate column to reference. For example, let's say you make a column called "Date Created". In this column you would add a column formula of:

=DATEONLY(Created@row)

Then you could reference "Created Date" wherever you need. For example, your formula would read

=IF(Frequency@row = "Weekly", [Date Created]@row + 7), IF(Frequency@row = "Daily", [Date Created]@row +1)

vanstadenster

Hi @SoS | Dan Palenchar

Thanks so much for your response. I have tried both and still getting UNPARSEABLE

It is as if a comma or a ) or something is in the wrong place.

Bit this seemed to work

=IF(Frequency@row = "Weekly", DATEONLY(Created@row + 7), IF(Frequency@row = "Daily", DATEONLY(Created@row + 1)))

vanstadenster

MMH even though I got this to work:

=IF(Frequency@row = "Weekly", DATEONLY(Created@row + 7), IF(Frequency@row = "Daily", DATEONLY(Created@row + 1)))

Now if i try to add Monthly, or BiAnnually or Annually it breaks again. Not really sure 🤣. These formulas breaks my brain.

=IF(Frequency@row = “Monthly”, DATEONLY(Created@row + 30), IF(Frequency@row = "Weekly", DATEONLY(Created@row + 7), IF(Frequency@row = "Daily", DATEONLY(Created@row + 1)))

Please help me. I am going in circles with this.

vanstadenster

@SoS | Dan Palenchar - just checking if you can help me resolve this:

I got the below to work

=IF(Frequency@row = "Weekly", DATEONLY(Created@row + 7), IF(Frequency@row = "Daily", DATEONLY(Created@row + 1)))

However if i try to add Monthly, or BiAnnually or Annually it breaks again. Not really sure 🤣 how to resolve this.

=IF(Frequency@row = “Monthly”, DATEONLY(Created@row + 30), IF(Frequency@row = "Weekly", DATEONLY(Created@row + 7), IF(Frequency@row = "Daily", DATEONLY(Created@row + 1)))

Andrée Starå

https://community.smartsheet.com/discussion/comment/500205#Comment_500205

Hi,

I hope you're well and safe!

Try this.

=IF(Frequency@row = "Monthly", DATEONLY(Created@row + 30), IF(Frequency@row = "Weekly", DATEONLY(Created@row + 7), IF(Frequency@row = "Daily", DATEONLY(Created@row + 1)))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with 💡 ⬆️ ❤️ and/or ✅ Answer. This will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

vanstadenster

Thank you. @Andrée Starå that helps.

I am trying to figure out what automation to run between my sheets. I currently have it to run daily based on when date is reached. and to move ti across to my master sheet. Based on Frequency and what time period due date is. But will it now copy it across 5 x times as it sees it in the range every day for 5 days. Is there a smarter way to do this.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.