Military Time Calculation: Night Shift

Options

I have columns set up in 4-digit military time. The formula below calculates day shift (0800 to 1700), but returns a negative value when calculating night shift (2200 to 0600). How can this be resolved?

=((VALUE(LEFT([End Time 1]@row, 2)) + VALUE(RIGHT([End Time 1]@row, 2)) / 60) - (VALUE(LEFT([Start Time 1]@row, 2)) + VALUE(RIGHT([Start Time 1]@row, 2)) / 60))

Thank you,

Morgan

Best Answers

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭
    Answer ✓
    Options

    Morgan;

    Try this (it probably needs some error checking to clean it up):

    =IF([End time 1]@row > [Start time 1]@row, ((VALUE(LEFT([End time 1]@row, 2)) + VALUE(RIGHT([End time 1]@row, 2)) / 60) - (VALUE(LEFT([Start time 1]@row, 2)) + VALUE(RIGHT([Start time 1]@row, 2)) / 60)), (24 + ((VALUE(LEFT([End time 1]@row, 2)) + VALUE(RIGHT([End time 1]@row, 2)) / 60) - (VALUE(LEFT([Start time 1]@row, 2)) + VALUE(RIGHT([Start time 1]@row, 2)) / 60))))

    Just beware, right now a zero minute shift will look 24 hours long. You probably need to nest another IF to deal with that!

    Dale

  • Morgan Huber
    Morgan Huber ✭✭
    Answer ✓
    Options

    DMurphy,

    Thank you... it worked. Now about that pesky additional nested IF

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Try this:

    =IF(VALUE([start time]@row)<=value([end time]@row, VALUE(LEFT([End Time 1]@row, 2)) - VALUE(LEFT([Start Time 1]@row, 2), 24 -VALUE(LEFT([Start Time 1]@row, 2) + VALUE(LEFT([End Time 1]@row, 2))) + ABS((Value(Right([start time]@row, 2)) -value(right([end time]@row, 2)))/60

    It will give you hours and fractions between the 2 military times.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Morgan Huber
    Options

    Hi Mark,

    The formula returned an error of #UNPARSEABLE.

    Morgan

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭
    Answer ✓
    Options

    Morgan;

    Try this (it probably needs some error checking to clean it up):

    =IF([End time 1]@row > [Start time 1]@row, ((VALUE(LEFT([End time 1]@row, 2)) + VALUE(RIGHT([End time 1]@row, 2)) / 60) - (VALUE(LEFT([Start time 1]@row, 2)) + VALUE(RIGHT([Start time 1]@row, 2)) / 60)), (24 + ((VALUE(LEFT([End time 1]@row, 2)) + VALUE(RIGHT([End time 1]@row, 2)) / 60) - (VALUE(LEFT([Start time 1]@row, 2)) + VALUE(RIGHT([Start time 1]@row, 2)) / 60))))

    Just beware, right now a zero minute shift will look 24 hours long. You probably need to nest another IF to deal with that!

    Dale

  • Morgan Huber
    Morgan Huber ✭✭
    Answer ✓
    Options

    DMurphy,

    Thank you... it worked. Now about that pesky additional nested IF

  • Morgan Huber
    Options

    DMurphy,

    Perfection! You're the best!

    Thank you so much!

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭
    Options

    Morgan;

    Great! Happy to help.

    Btw, you should be able to convert that into a column formula to make it more reliable.

    Cheers,

    Dale

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!