Help adding to formula to include Satruday/Sunday dates change to preceding Friday

I currently have a formula for a 'due date' column that looks at another date column "DOD" and calculates if the status is 'probability' for that row it makes the 'due date' column "DOD" + 5 years, otherwise the 'due date' column is 8 months + 1 day from the "DOD" date column, adjusting for the next year if the 8 months + 1 day crosses into a new month. This is the formula:

=IF(Status@row = "Portability", DATE(YEAR(DOD@row ) + 5, MONTH(DOD@row ), DAY(DOD@row )), DATE(YEAR(DOD@row ) + INT((MONTH(DOD@row ) + 8) / 12), MOD(MONTH(DOD@row ) + 8, 12) + 1, DAY(DOD@row )))

I want to add a condition that if the final date after the calculation in the formula above is a Saturday or Sunday, the date adjusts to the preceding Friday. I have tried using the Smartsheet AI, Chat GPT, and a person who does a lot of work in SQL. These options have produced 10 different formulas that all have failed. I either get syntax errors or values in the date column saying INVALID#. Any suggestions?

Find more posts tagged with

Accepted answers

Darren Mullen

@MRKBADGER You can use the WEEKDAY function to check which day of the week the resulting date is, and then adjust 1 or 2 days as needed.

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

Paul Newcome

You can do this all in one formula, but any tweaks would be a pain to manage. I would suggest a second column to evaluate this "Initial Due Date" output with a "Final Due Date" formula along the lines of

=[Initial Due Date]@row - IF(OR(WEEKDAY([Initial Due Date]@row ) = 1, WEEKDAY([Initial Due Date]@row ) = 7), MOD(WEEKDAY([Initial Due Date]@row ), 7) + 1, 0)

All comments

Darren Mullen

@MRKBADGER You can use the WEEKDAY function to check which day of the week the resulting date is, and then adjust 1 or 2 days as needed.

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

Paul Newcome

=[Initial Due Date]@row - IF(OR(WEEKDAY([Initial Due Date]@row ) = 1, WEEKDAY([Initial Due Date]@row ) = 7), MOD(WEEKDAY([Initial Due Date]@row ), 7) + 1, 0)

MRKBADGER

Thank you. That is a nice, simple solution.

Paul Newcome

Happy to 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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.