Sum hours of each shift during given dates

User in my local excel forum needs to sum how many hours per shift during given range of dates, as following:

A3: starting date
B3: ending date
C3: hours of shift 1
D3: hours of shift 2
E3: hours of shift 3

with below example:

Please Login or Register  to view this content.

The range of dates may be within 1 day, over 1 month or over 1 year.

I am in stuck for a while and need something to start with.

Thanks in advance.

Sorry azumi, it did not work.
Shift 1,2,3 return 0,1,1 (hours) while 58:30, 24:00, 26:25 is expected.

Hi bebo,

Sorry, but I don't understand how you arrive at your expected results. Could you clarify?

Regards

Thank XOX LX,
For example:

From A3= 01/04/2014 3:35:00
To B3= 05/04/2014 16:30:00

I need to sum number of hours spent through out
i.e, for Shift 1: from 6:01 to 18:00

01/04/2014: 12 hours (from 6:01 to 18:00)
02/04/2014: 12 hours (from 6:01 to 18:00)
03/04/2014: 12 hours (from 6:01 to 18:00)
04/04/2014: 12 hours (from 6:01 to 18:00)
05/04/2014: 10.5 hours (from 6:01 to 16:30)

Total: 58.5 (or 58:30) hours for this shift.

Similar to shift 2 and 3.

Hope it make sense.

Thank, zbor. I'll give it a try.

@Zbor,
Almost done. Except:
A3=01/04/2014 3:35:00 AM
B3=05/04/2014 5:30:00 AM
G3 (6:01 to 18:00) = 47:30

I expect G3 to be 4 days (1,2,3,4/04/2014) * 12 hours = 48:00

Any sugesstion?

Thanks zbor. It works like a charm.