Vacation Tracker - half day entry requirement

Options

Hi everyone,

I found a great youtube video that helped me build a vacation tracker for my business. Only problem is the tracker covers only full day vacation requests.

Could any of our great experts help me to amend the below formula to incorporate a half day vacation request? I guess I would need to add a column into the vacation request tracker grid which could be a tick box "Half Day Request?" and then add this question into the vacation request form. The user would state a vacation request start and end date (which would be the same date), and then the user would tick or say yes to the "do you require a half day vacation?". The formula then needs to consider that if the "half day request" box is ticked or says yes, then it should only note 0.5 days used. If the box isn't ticked or says no, then the formula should note 1 day of vacation used.

=IF(AND(YEAR([Vacation Start]@row) = 2023, YEAR([Vacation End]@row) = 2024), NETWORKDAYS([Vacation Start]@row, [Endof2023]#), IF(AND(YEAR($[Vacation Start]@row) = 2023, YEAR($[Vacation End]@row) = 2023), NETWORKDAYS($[Vacation Start]@row, [Vacation End]@row), 0))

I hope I've managed to explain clearly but if anyone can help and needs a bit more info - I'll be happy to provide.

Thanks in advance and best regards,

Ahmet

Tags:

Best Answer

  • Julie Fortney
    Julie Fortney Overachievers
    Answer ✓
    Options

    @Ahmet - newby

    Here's what I would do:

    Add the half-day checkbox as an optional field in your form, and add form logic to display the end date field only when the box is not checked. (So if the user selects the half-day checkbox, the end date field disappears.)

    Then for your formula, add a simple IF statement at the beginning to return a value of 0.5 if the half day box is checked.

    =IF([half day?]@row=true, 0.5,IF(AND(YEAR([Vacation Start]@row) = 2023, YEAR([Vacation End]@row) = 2024), NETWORKDAYS([Vacation Start]@row, [Endof2023]#), IF(AND(YEAR($[Vacation Start]@row) = 2023, YEAR($[Vacation End]@row) = 2023), NETWORKDAYS($[Vacation Start]@row, [Vacation End]@row), 0)))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!