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!
Best Answer
-
Hi @Audrey Howard - First, you'll need to define Working Days in your 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)))))))
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist | Process Improvement Enthusiast
Answers
-
Hi @Audrey Howard - First, you'll need to define Working Days in your 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)))))))
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist | Process Improvement Enthusiast
-
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.
-
This formula worked, thank you!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!