Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)


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!

share|improve this question

closed as too localized by Shinrai, Dave, BBlake, Indrek, 8088 Oct 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
up vote 2 down vote accepted

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

enter image description here

share|improve this answer
I had no idea - thank you so much! – Tom Oct 15 '12 at 15:00

Not the answer you're looking for? Browse other questions tagged or ask your own question.