How to calculate duration based on workdays, and deduct 0.5 days
Hello, I would like to track planned leave and duration for my team. On my sheet, I have "Start Date", "Half Day Start", "End Date"; and "Half Day End" columns.
If Start Date is Mon 24-Jun-2024 and Half Day Start is Ticked; and
End Date is Wed 26-Jun-2024 and Half Day End is Ticked, the Duration should be 2 days
I am using this formula
=NETDAYS([Start Date]@row, [End Date]@row) + IF([Half Day Start]@row = "Yes", -0.5, 0) + IF([Half Day End]@row = "Yes", -0.5, 0)
but the duration is showing as 3d, which is incorrect. How can I fix this please?
Best Answer
-
The check box is binary. So change the "Yes" to a 1. see how that helps.
=NETDAYS([Start Date]@row, [End Date]@row) + IF([Half Day Start]@row = 1, -0.5, 0) + IF([Half Day End]@row = 1, -0.5, 0)
How ever if you run into an issue that includes weekends I would use NETWORKDAYS instead.
=NETWORKDAYS([Start Date]@row, [End Date]@row) + IF([Half Day Start]@row = 1, -0.5, 0) + IF([Half Day End]@row = 1, -0.5, 0)
If you want to be able to do a column formula. Change your column from duration to text/number and at the end of your formula put +"d".
The Image below is using the text/number columns instead of duration columns.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Answers
-
The check box is binary. So change the "Yes" to a 1. see how that helps.
=NETDAYS([Start Date]@row, [End Date]@row) + IF([Half Day Start]@row = 1, -0.5, 0) + IF([Half Day End]@row = 1, -0.5, 0)
How ever if you run into an issue that includes weekends I would use NETWORKDAYS instead.
=NETWORKDAYS([Start Date]@row, [End Date]@row) + IF([Half Day Start]@row = 1, -0.5, 0) + IF([Half Day End]@row = 1, -0.5, 0)
If you want to be able to do a column formula. Change your column from duration to text/number and at the end of your formula put +"d".
The Image below is using the text/number columns instead of duration columns.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Thank you very much! Problem solved!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!