# 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

• ✭✭✭✭✭✭
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

Options

DMurphy,

• ✭✭✭✭✭✭
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.

• Options

Hi Mark,

The formula returned an error of #UNPARSEABLE.

Morgan

• ✭✭✭✭✭✭
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

Options

DMurphy,

• Options

DMurphy,

Perfection! You're the best!

Thank you so much!

• ✭✭✭✭✭✭
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!