Military Time Calculation: Night Shift

Morgan Huber

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

Dale Murphy

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