Software

Ask something
Discussion Locked

Excel 24 hour clock using 100 minutes

0 helpful
4 replies

by gfelts

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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"

4 replies

by simy

Try this formula:

=(D2-C2)+((B2+2400)-A2)

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.

by dklippert

"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:

[h]:mm

See Chip Pearsons Date-Time discussions at CPearson.com:

http://tinyurl.com/39qen

by gfelts

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.
Greg

This discussion is closed to new replies.