Calculating the number of workdays between two dates, even if the dates are after business hours
Hi,
I have a formula to calculate the business days between the date REQUESTED for a document, and the date ALL COMPLETE. It works fine unless the document is requested after 5pm and/or completed after 5pm PST. For instance, if the Request is 5:30 pm, and the Complete is 6:30pm same day, I’m getting -2, instead of getting 1 business day.
Any suggestion on how to adjust my formula so that the -2 is converted to “1” ?
=IFERROR(NETWORKDAYS(Requested@row, [All Complete]@row), " ")
The IFERROR is there because sometimes there are notes in the Complete cells (which is a date column), so I want those cells to be blank.
I get the same -2 if I use “NETDAYS” or “NETWORKDAYS”
I’ve tried adding various AND formulas, but it kept getting an error.
Thank you for your assistance.
Answers
-
Hello @Misha A
Upon reviewing your formula & testing this in my own sheet, I adjusted the formula you provided because it is giving me an #unparseable formula error message. After adjusting the formula, same day dates are giving me "1" instead of "-2". See screenshots of what I have done.
I used NETDAYS & NETWORKDAYS
=IFERROR(NETWORKDAYS(REQUESTED@row, [All Complete]@row), "")
=IFERROR(NETDAYS(REQUESTED@row, [All Complete]@row), "")
Visit the links below for more information.
If the above doesnt resolve the issue, please provide us additional screenshots of what you have setup. If you could convert that column into a cell formula and provide us the formula being used there. (Please block out any sensitive data)
Cheers,
Krissia
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!