Updating current formula from counting days to counting business days

Hello! I have been tasked with creating a formula that looks at the request type (drop down) and based on that, adds a certain number of business days to the request submitted date to give a completion date. There's 6 options, I was able to make the formula just with regular date counting, but now I need help in adding in the 'working days' formula into this, so the date that is calculated is skipping weekend days. For example, selecting option 1 would add 3 business days to the created date, option 2 would add 5 business days, etc.

=IF(CONTAINS("1-", [Order Type]@row), [Created Date]@row + 3, IF(CONTAINS("2-", [Order Type]@row), [Created Date]@row + 5, IF(CONTAINS("3-", [Order Type]@row), [Created Date]@row + 14, IF(CONTAINS("4-", [Order Type]@row), [Created Date]@row + 21, IF(CONTAINS("5-", [Order Type]@row), [Created Date]@row + 5, IF(CONTAINS("6-", [Order Type]@row), [Created Date]@row + 10))))))

It took me a while to get here, and it is working perfectly as a column formula, so don't want to completely start over/ reformat and risk messing it up again. just hoping to add something in here to count working days only

Thanks!

Find more posts tagged with

Formulas

Accepted answers

Amber Eakin

Hi @Audrey Howard - First, you'll need to define Working Days in your sheet.

https://help.smartsheet.com/articles/516392-defining-working-non-working-holidays-on-a-project-sheet

Once you have that, you can use this formula. Let me know if that helps!

=IF(CONTAINS("1-", [Order Type]@row), WORKDAY([Created Date]@row, 3), IF(CONTAINS("2-", [Order Type]@row), WORKDAY([Created Date]@row, 5), IF(CONTAINS("3-", [Order Type]@row), WORKDAY([Created Date]@row, 14), IF(CONTAINS("4-", [Order Type]@row), WORKDAY([Created Date]@row, 21), IF(CONTAINS("5-", [Order Type]@row), WORKDAY([Created Date]@row, 5), IF(CONTAINS("6-", [Order Type]@row), WORKDAY([Created Date]@row, 10)))))))

All comments

Amber Eakin

Hi @Audrey Howard - First, you'll need to define Working Days in your sheet.

https://help.smartsheet.com/articles/516392-defining-working-non-working-holidays-on-a-project-sheet

Once you have that, you can use this formula. Let me know if that helps!

Dakota Haeffner

To add the functionality of skipping non-working days, such as weekends, you can use the NETWORKDAYS function. This function calculates the number of working days between two dates, excluding weekends and any dates specified as holidays.

Here's an updated version of your formula that uses the NETWORKDAYS function to calculate the completion date:

=IF(CONTAINS("1-", [Order Type]@row), WORKDAY([Created Date]@row, 3), IF(CONTAINS("2-", [Order Type]@row), WORKDAY([Created Date]@row, 5), IF(CONTAINS("3-", [Order Type]@row), WORKDAY([Created Date]@row, 14), IF(CONTAINS("4-", [Order Type]@row), WORKDAY([Created Date]@row, 21), IF(CONTAINS("5-", [Order Type]@row), WORKDAY([Created Date]@row, 5), IF(CONTAINS("6-", [Order Type]@row), WORKDAY([Created Date]@row, 10))))))

In this formula, the WORKDAY function is used to add the specified number of working days to the created date. The first argument is the start date, and the second argument is the number of working days to add.

Note that you will need to make sure that your account's calendar settings are set up correctly in order for the NETWORKDAYS function to work properly.

Audrey Howard

https://community.smartsheet.com/discussion/comment/372246#Comment_372246

This formula worked, thank you!!

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.