Schedule update dates

Hi,

I'm looking at creating a function that lets the user know when the next review date for a row is, based on the Overall Status (not closed), Last Review Date and Review Frequency columns.

If possible I'd like to use Daily, Weekly etc., to make it a better experience for the user, rather than using the number of days (1, 7, 30 etc.,).

My thoughts are to use a helper column with an IF to select the number of days, then use the helper column value to update the Next Review Date.

Questions:

Is there an easier way to do this :-) ?
Will updating by the number of days work from this year to the next, or would I need to look at incrementing the year, month, week?
If the Next Review Date falls on a weekend, I want to move the date to the Monday of the following week.

Many thanks for any help.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

You wouldn't need a helper column, but you will need a nested IF to convert the text values into numbers.

=WORKDAY([Last Review Date]@row, IF([Review Frequency]@row = "Daily", 1, IF([Review Frequency]@row = "Weekly", 7, IF(..............))))))))

Head Scratch

Thanks for your help with this Paul,

I've used, the formula below - Last Review and Next Review columns are both date columns, but I get the #INVALID DATA TYPE error. Will I need to add to the formula?

=WORKDAY([Last Review Date]@row, IF([Review Frequency]@row = "Daily", 1, IF([Review Frequency]@row = "Weekly", 7, IF([Review Frequency]@row = "Fortnightly", 14,IF([Review Frequency]@row = "Monthly", 30, IF([Review Frequency]@row = "Quarterly", 90,IF([Review Frequency]@row = "Half Yearly", 182,IF([Review Frequency]@row = "Annually", 365))))))))

Head Scratch

Sorry, I hadn't put a date in the Last Review col 🙄.

Head Scratch

I've just given tis bit a quick test.

It is doing what it should do, but it is taking, the fortnightly one as 14 days (correctly) but it adds 14 work days (as it should).

How would I do it so, for say the fortnightly one in the example below, the Next review date would be 15/05/23 (10 work days). Is it as simple as taking off the number of Saturdays and Sundays from each of the frequencies (so the number of days for the Fortnightly one becomes 10)? Or is there a way to do it using the formula?

As ever, thanks for the help.

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.