Calculate hours worked per day where we have multiple shifts per day and shifts span 12pm

I am trying to calculate hours worked per day. The problem that has me stumped is how to handle multiple shifts per day and times that span midnight. I understand that Excel might not be (perhaps there is no might about it, it's not) the best tool for the job, but if there is a brilliant way to crack this I am sure one of you can supply what I need. I also understand that this question is probably asked and answered, but it is hard to search for and I have not found an answer.

Thanks!

Please Login or Register  to view this content.

I don't think you need anything too tricky, just format your cells properly. The time in/out cells should be formatted as m/d/yyyy h:mm (under 'Custom'), while the subtraction row to find the hours should be formatted as h:mm (also under custom. Then just use subtraction: =C2-B2 to get the hours worked each shift.

Originally Posted by ChemistB

Are you looking for the Total for User U03? or the Total per row or the total per day?
If it's total per day, when they work across midnight, do you put the entire amount into the first date or split it at midnight?

I am looking for the total worked per day. So:

Please Login or Register  to view this content.

(I did these calcs manually so I am hoping I got them right)

Originally Posted by cantosh

I don't think you need anything too tricky, just format your cells properly. The time in/out cells should be formatted as m/d/yyyy h:mm (under 'Custom'), while the subtraction row to find the hours should be formatted as h:mm (also under custom. Then just use subtraction: =C2-B2 to get the hours worked each shift.

Hours worked per shift gets part of the way there, but I need to split the time between two days if a shift spans a day.

DayWorked: G2:G4
try this...
=SUM(IF(INT($B$2:$B$5)=G2,IF($C$2:$C$5<G2+1,$C$2:$C$5,G2+1)-$B$2:$B$5,IF(INT($C$2:$C$5)=G2,MOD($C$2:$C$5,1),0)))
Enter with Ctrl+Shift+Enter
Copy down