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

Morgan Huber

DMurphy,

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

Dale Murphy

@Morgan Huber

=IF([End time 1]@row = [Start time 1]@row, 0, (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))))))

... should deal with it ...

Cheers,

Dale

Mark Cronk

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

Morgan Huber

Hi Mark,

The formula returned an error of #UNPARSEABLE.

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

Morgan Huber

DMurphy,

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

Dale Murphy

@Morgan Huber

=IF([End time 1]@row = [Start time 1]@row, 0, (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))))))

... should deal with it ...

Cheers,

Dale

Morgan Huber

DMurphy,

Perfection! You're the best!

Thank you so much!

Dale Murphy

Morgan;

Great! Happy to help.

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

Cheers,

Dale