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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!