#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Vacation days formula; how to allow for half-days (or holidays)

Options
✭✭✭✭✭
edited 12/09/19

Not having a lot of background experience with formulas, I am having difficulty determining whether or not the NETWORKDAYS formula can be "adjusted" to include an allowance for half-days.

Using the Vacation template, it calculates the number from start to end days. I have added a check-box column for half-days. If this box is checked, I want it to subtract .5 days from the 1 day total (created by the NETWORKDAYS formula). Clearly I am doing something wrong because the formula comes back as UNPARSEABLE.

Is it possible to create the formula noted above and, if so, where am I going wrong with the formula below?

=(NETWORKDAYS([Start Date]63, [End Date]63) - IF([Half-day]63, .5_if_true, 0_if_false))

Tags:

• edited 03/22/16
Options

Pam,

You want your if statement first, try this using your column names:

=IF([Column38]11 = "True", NETWORKDAYS([Column36]11, [Column37]11) - 0.5, NETWORKDAYS([Column36]11, [Column37]11))

Christine

• ✭✭✭✭✭
edited 03/22/16
Options

That worked perfectly - thank you! (Not to mention the formula actually makes sense.)

• ✭✭✭✭✭
Options

Okay, one more. I have copied and adjusted that formula to perform a similar function when a holiday occurs within a vacation period (thereby subtracting 1 day from the total). Is it possible to add the two formulas together

=IF([Half-day]63 = "True", NETWORKDAYS([Start Date]63, [End Date]63) - 0.5,NETWORKDAYS([Start Date]63, [End Date]63))

PLUS

=IF([Holiday amidst vacation]62 = "True", NETWORKDAYS([Start Date]62, [End Date]62) - 1, NETWORKDAYS([Start Date]62, [End Date]62))

where the end result is NETWORKDAYS - .5 (for a half-day) - 1 (for a holiday) [that could be minus 1.5 if both statements are TRUE), and minus 0 if both statements are FALSE?

• Options

You can use If(And(. Don't forget to write for if both are true and then if only one is true but not the other.

• ✭✭✭✭✭
Options

Thank you, Christine!

• ✭✭✭✭✭
Options

Wow, that one took me a while to work out. However, good news - I managed to finally figure it out, and it worked!

This discussion has been closed.