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:

    Start date	     End date	                                           TOTAL HOURS		
		                        Shift 1: 6:01-18:00       Shift 2: 18:01-24:00	               Shift 3: 00:01-06:00
01/04/2014 3:30	----05/04/2014 16:30	----- 58:30	------------------- 24:00	------------------- 26:25

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.

Here.. I've just changed order so lowest times are first. This way you can just change times in header to get other values.

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?

Here:

Formula:

=G$2-MAX(F$2;MOD($A3;1))+MIN(G$2;MAX(MOD($B3;1);F$2))-F$2+(G$2-F$2)*(INT(1+$B3)-INT(1+$A3)-1)

Change ; to ,

Thanks zbor. It works like a charm.