Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

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.

1  
What have you tried so far? –  CharlieRB Oct 15 '12 at 14:23
3  
-1 Tom, are you aware that your questions read like "I want to do this, I can't be bothered, some one else can do it for me"... and as such, you may not get a great response (coupled with a 55% accept rate)! Your post also lacks any detail- does it have to be 4 columns (I assume based upon your OP), or should it be total day in 1 column and a total breaks in another - do you need this all broken down. –  Dave Oct 15 '12 at 14:30
    
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 1

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.