Round returned date to working day?

I've been using a formula to calculate due dates, which are always 90 calendar days from the original date column. This is the formula: =IF([Resubmission Required?]@row = "true", [Date Decided (Approved/Rejected)]@row + 90, "n/a")

However, because we are required to calculate these dates in calendar days, many of the dates returned fall on a Saturday or Sunday. Is it possible to write a formula that not only calculates the "technical" due date (+90 calendar days) but will also round the date BACK to the last workday (Friday) if the returned date falls on a weekend? Happy to provide more details if needed.

Find more posts tagged with

Formulas

Accepted answers

L_123

I tried to get fancy with it, and it's possible to calculate using mod(), substitute(), and a variety of other equations, but a simply replacement of the weeknumber with an appropriate negative ends up much simpler.

=IF([Resubmission Required?]@row = "true", [Date Decided (Approved/Rejected)]@row + 90 + IF(weekday([Date Decided (Approved/Rejected)]@row + 90) = 1, -2, IF(weekday([Date Decided (Approved/Rejected)]@row + 90)= 7, -1, 0)) , "n/a")

All comments

L_123

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.