# 24 hour conversion to decimal [closed]

I figured it out _ I had to change the cell format to number and add *24

Using the following =SUM(MOD(C6-B6,1)+(E6-D6)) I get a correct 24hr sum of time (9.30) however I need that in decimal format so I use =(E6-INT(E6))*24 to convert it to (9.5)

I'm having problems trying to combine into one formula - any help would be appreciated.

I'm having problems figuring hours worked, with a 24 hour day formula that accounts for swing and graveyard this is a 4 column layout - example below (I only need to figure totals hours worked minus Lunch)

example:

in 20:30 out (lunch) 01:00 in(lunch) 01:30 end of shift 06:30

Answer should be: should be 4.5+5 = 9.5 hours worked However I'm having trouble with 20:30pm to 01:00am value

thank you in advance!

-

## closed as too localized by Shinrai, Dave, BBlake, Indrek, 8088Oct 15 '12 at 16:24

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

non 24 hr example: =SUM(B4-A4)+(D4-C4) 11:30 16:00 16:30 20:00 = 8:00 – Tom Oct 15 '12 at 14:57
@Tom please edit & add it to your question, not as a comment. Thanks – Sathya Oct 15 '12 at 16:10

## 1 Answer

If you use the `MOD` function within your `SUM` function you can calculate the time which crosses midnight.

-
I had no idea - thank you so much! – Tom Oct 15 '12 at 15:00