NETWORKDAYS FORMULA
Hello,
I am trying to construct a formula to calculate the number of business days between two date columns (date received and date entered into system, for example) within a specific date range (from 02/18/2022 - 03/18/2022).
How can I accomplish this if at all possible.
Please help. Thank you!
Answers
-
Which column or columns have your date range?
You essentially need an IF or IF/AND to determine if the date(s) fall into the desired range, then have a NETWORKDAYS as the positive condition. Something like this:
=IF(AND(DateEntered@row > DATE(2022, 2, 18), DateReceived@row < DATE(2022, 3, 18)), NETWORKDAYS(DateEntered@row, Datereceived@row), "")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
The formula worked! Thank you.
On another note, is there a way that you can tell a cell to remain blank rather than provide an error message (i.e., divide by zero) so that if you have multiple formulas relying on each other, kind of like a domino effect, it won't disable the following formulas from working.
-
Glad it worked.
The IFERROR function can help you with that. The syntax is:
=IFERROR(your formula goes here), alternate value if there's an error)
In use:
=IFERROR(IF(AND(DateEntered@row > DATE(2022, 2, 18), DateReceived@row < DATE(2022, 3, 18)), NETWORKDAYS(DateEntered@row, Datereceived@row), ""), "These aren't the droids you're looking for.")
Or you can just use , "") at the end, for a blank value.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you! You're a genius.
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!