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

Pam Tadsen

 

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

 

Thanks in advance!

Christine Oakes

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

Pam Tadsen

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

Pam Tadsen

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?

Christine Oakes

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.

 

Pam Tadsen

Thank you, Christine!

Pam Tadsen

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