Counting Days with plus or minus options
I know, I know… use =Networkdays([start date]1, [end date]1)
HOWever, I am trying to track the performance of when a job is completed according to its due date. I found adding a -1 to to stop the inclusiveness of the calculation, whereas I can get a job completed on 10/5 that was due 10/6 to show 1 day using
=NETWORKDAYS([Completed Date]1, [DEPLOY DATE]1 - 1)
or
=NETWORKDAYS([Completed Date]1, [DEPLOY DATE]1) - 1
The problem occurs if a job is completed on 10/7 for that same job. Because the default inclusive answer becomes -2, when it applies the -1 to it, it shows -3.
Is there a way to incorporate an IF/THEN type statement to subtract 1 from the inclusive answer if it's positive, and add 1 to the inclusive answer if it's negative?
Best Answer
-
Try something like =IF(NETWORKDAYS([Completed Date]1, [DEPLOY DATE]1) > 0, NETWORKDAYS([Completed Date]1, [DEPLOY DATE]1) + 1, NETWORKDAYS([Completed Date]1, [DEPLOY DATE]1) - 1) Hope this helps!
Answers
-
Try something like =IF(NETWORKDAYS([Completed Date]1, [DEPLOY DATE]1) > 0, NETWORKDAYS([Completed Date]1, [DEPLOY DATE]1) + 1, NETWORKDAYS([Completed Date]1, [DEPLOY DATE]1) - 1) Hope this helps!
-
@Adam Murphy - Yep. Thank you so much! That's the direction I was headed, but it was hurting my brain trying to figure it out. Had to tweak a little bit, but got it to work perfectly. :)😊
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!