Vacation Tracker - half day entry requirement

Ahmet - newby · 11/14/23

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

Julie Fortney · 11/15/23

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

Julie Fortney · 11/15/23

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

Ahmet - newby · 11/16/23

Ahh - so simple yet so effective - I think I was overthinking it :)

It worked - thank you so much...

Julie Fortney · 11/16/23

@Ahmet - newby You're welcome! (from a fellow overthinker :)

Evan Fowler · 11/23/23

@Ahmet - newby would you mind sharing the YouTube video you found? I am also trying to build a Vacation Tracker in SmartSheets but I have not been able to find anything that does what I am looking for. I am very shocked that SmartSheets doesn't already have a useable Template for this.