Network Days where same day = 0
I want to know the number of working days between two dates but when the two dates are the same day I would expect it to report 0 but instead its reporting 1. For my purposes items done same day would be a 0.
Items completed a day later would be 1 day and so on.
Answers
-
You could just add -1 at the end of the formula to subtract 1 day. Add [Holidays] after end date if you want to account for them as well.
=NETWORKDAYS([Start Date]@row, [End Date]@row) - 1
-
Hey @Jeff M.
Thanks! I thought it mighb e something like that. I am curious about the [Holidays] portion. Do I need to list them somewhere else with the dates. I can give this a google to see if there is a tutorial on it might be easier to follow than you trying to type it out to me.
Thanks!
-
My suggestion for the holidays would be to list them down a column on a separate sheet and then select this column as a cross sheet reference.
-
=IFERROR(NETWORKDAYS([Actual Start Date]@row, [Sub-Task Completion Date]@row), "")
This is my current formula. How would i get it to subtract 1 day so that if my start and completion dates are the same will = 0
-
Try something like this...
=IFERROR(NETWORKDAYS([Actual Start Date]@row, [Sub-Task Completion Date]@row) - IF(AND([Actual Start Date]@row <> "", [Actual Start Date]@row = [Sub-Task Completion Date]@row), 1), "")
-
@Paul Newcome ok so that does produce a zero if the dates are the same in both cells but the issue is that i need it to subtract 1 day for all.
For example i have
Actual Start Date: 10/20/21
Completion Date: 10/21/21
I want this to produce "1" not "2" like it is. Because is still counting 10/20 as its own day.......
-
If you want it to subtract one from all then you should be able to use this...
=IFERROR(NETWORKDAYS([Actual Start Date]@row, [Sub-Task Completion Date]@row) - 1, "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!