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)

Pam Tadsen
Pam Tadsen ✭✭✭✭✭
edited 12/09/19 in Archived 2016 Posts

 

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!

Tags:
· Share on FacebookShare on Twitter

Comments

  • Christine Oakes
    Christine Oakes ✭
    edited 03/22/16

    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

    · Share on FacebookShare on Twitter
  • Pam Tadsen
    Pam Tadsen ✭✭✭✭✭
    edited 03/22/16

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

    · Share on FacebookShare on Twitter
  • Pam Tadsen
    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?

    · Share on FacebookShare on Twitter
  • Christine Oakes
    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.

     

    · Share on FacebookShare on Twitter
  • Pam Tadsen
    Pam Tadsen ✭✭✭✭✭

    Thank you, Christine!

    · Share on FacebookShare on Twitter
  • Pam Tadsen
    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!

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories