DATE Formula: How to calculate a half-day?
I have a Paid Time Off (PTO) tracker, whereby the company allots a full day's pay for major U.S. holidays - and a half day's holiday pay on New Year's Eve (Dec. 31st).
Below is the applicable part of the formula: Employees submit a form requesting how many days they want off, what the start date is and what the last date is. If their requested days off fall within one of the "DATE"s shown below, that date is not subtracted from their PTO allotted days (for example, if Jan. 12th was the start date of their PTO and Jan. 16 was the last date, Smartsheet would only deduct two days from their PTO, since Jan. 13-14 is the weekend and Jan. 15 is a paid holiday).
What can I insert into this formula to only subtract a half day on Dec. 31st? I've tried variations of SUM(DATE(2024,12,31)-.5 without luck. I appreciate your help with this!
IF([PTO Days Requested By Employee]@row > 0, NETWORKDAY([Start Date of PTO]@row, [Last Date of PTO]@row, DATE(2024, 1, 1), DATE(2024, 1, 15), DATE(2024, 5, 27), DATE(2024, 7, 4), DATE(2024, 9, 2), DATE(2024, 11, 28), DATE(2024, 12, 24), DATE(2024, 12, 25), DATE(2024, 12, 31)))))
Answers
-
Hi @Sally Wico
You can use the IF statement to subtract half-day conditionally.
=IF([PTO Days Requested By Employee]@row > 0, NETWORKDAY([Start Date of PTO]@row, [Last Date of PTO]@row, DATE(2024, 1, 1), DATE(2024, 1, 15), DATE(2024, 5, 27), DATE(2024, 7, 4), DATE(2024, 9, 2), DATE(2024, 11, 28), DATE(2024, 12, 24), DATE(2024, 12, 25), DATE(2024, 12, 31))) - IF(AND([Start Date of PTO]@row <= DATE(2024, 12, 31), [Last Date of PTO]@row >= DATE(2024, 12, 31)), 0.5, 0)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K 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!