DATE Formula: How to calculate a half-day?

Sally Wico

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)))))

jmyzk_cloudsmart_jp

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)

https://app.smartsheet.com/b/publish?EQBCT=60fa3faabc2c4ec88dfd09061eb7b558