General discussion


Excel 24 hour clock using 100 minutes

Working for the Govt I have to cover 24 hour shifts that use a 100 minute clock. (i.e. 0715 is 0725 on 100 minute clock.) 2330 is 2350 on 100 minute clock. The problem I am having is night shift comes in at 2250 (1030pm) and get off work the next morning at 0750 (0730am). I have to convert the times manually then enter them into a spread sheet that tracks hours worked. Any idea how to count the time that goes from one day to the next. Any help would be greatly appreciated. I would like to be able to enter Begin Tour on Monday evening (BT=2250) in Col "A" Out to lunch (OL=0250) in Col "B" In from Lunch (IL=0300) in Col "C" and End of Tour on Tuesday Morning (ET=0700) in Col "D". Then have Excel Put the total time worked in Col "E"

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by SimY In reply to Excel 24 hour clock using ...

Try this formula:


The values are in the columns specified and in row 2.

There are 2 parts to this:
1) D2-C2 gives you the period worked after lunch i.e. 700-300 = 400.
2) B2+2400 allows you to subtract it from 2250 to give you the period before lunch i.e. 2650-2250 = 400

Add 1) and 2) and you have the total hours worked in 100 minute clock - 800 = 8 hours

Hope this is what you're after.

Collapse -

by DKlippert In reply to Excel 24 hour clock using ...

"Regular time can be converted to decimal minutes using this formula:

=HOUR(A 1)+(MINUTE(A1)/60)

When dealing with times that span days, use this format for your answers:


See Chip Pearsons Date-Time discussions at

Collapse -

by GFELTS In reply to

I have used this formula in the past and have no idea why I didn't hink of it using it this time. Thanks for the help and the brain jog.

Collapse -

by GFELTS In reply to Excel 24 hour clock using ...

This question was closed by the author

Related Discussions

Related Forums