Total hours on call over holidays and shifts

[DRSteele]

Can someone please help me?

I'm trying to create formulas to calculate the total number of hours each man is on call, segregated into the number of hours on call during waking hours, during sleeping hours, during waking hours on a holiday and during sleeping hours on a holiday. A period of being on call can span several days.

Here is some sample data and an exhibit of desired output. I include a column that illuminates how I manually calculated the hours for Man 5. [I cannot use VBA for this solution, but if one is available please do post it for the benefit of those who can.]

	A	B	C	D	E	F	G
1	Bedtime	22:00
2	Awaken	6:00
3
4	Holidays	12/25/2016
5		12/26/2016
6		1/1/2017
7
8	On Call	Man 1	Man 2	Man 3	Man 4	Man 5
9	In	12/23/2016 21:00	12/24/2016 09:00	12/24/2016 15:00	12/25/2016 23:00	12/26/2016 22:00
10	Out	12/24/2016 03:00	12/24/2016 17:00	12/25/2016 23:00	12/26/2016 12:00	01/03/2017 04:00
11	total hours	6	8	32	13	174
12
13	Hours On Call	Man 1	Man 2	Man 3	Man 4	Man 5
14	Awake	1	8	7	0	96	=27,28,29,30,31,2 x16 hrs
15	Asleep	5	0	2	0	52	=27,28,29,30,31,2 x8hrs + 3x4hrs
16	Holiday Awake	0	0	22	6	16	=1 x 16hrs
17	Holiday Asleep	0	0	1	7	10	=26 x2hrs + 1 x8hrs
18	total hours	6	8	32	13	174

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet22​

 

[Chrisdontm]

I would like to offer a solution for you, but make a copy first and try it out.

I will give you formulas for column " F " and then copy them over to the left. ( I hope I thought of everything )

F14 =IF(TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))>=$B$1,0,IF(TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))<$B$2,(($B$1-$B$2)*24),($B$1-TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9)))*24))+IF(TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))<=$B$2,0,IF(TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))>$B$1,(($B$1-$B$2)*24),(TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))-$B$2)*24))+(INT(F$10)-INT(F$9)-1)*(($B$1-$B$2)*24)-F$16

F15 =IF(TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))>=$B$1,24-TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))*24,24-($B$1*24))+IF(TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))<$B$2,($B$2-TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9)))*24,0)+(INT(F$10)-INT(F9)-1)*((24-($B$1*24))+($B$2*24))+IF(TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))>=$B$2,($B$2*24),(TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))*24))+IF(TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))>$B$1,(TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))-$B$1)*24,0)-F17

F16 =IF(INT(F$9)=$B$4,IF((TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))<$B$1),IF((($B$1-(TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))))*24)>($B$1-$B$2)*24,($B$1-$B$2)*24,($B$1-(TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))))*24),0),0)+IF(INT(F$9)=$B$5,IF((TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))<$B$1),IF((($B$1-(TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))))*24)>($B$1-$B$2)*24,($B$1-$B$2)*24,($B$1-(TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))))*24),0),0)+IF(INT(F$9)=$B$6,IF((TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))<$B$1),IF((($B$1-(TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))))*24)>($B$1-$B$2)*24,($B$1-$B$2)*24,($B$1-(TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))))*24),0),0)+IF(INT(F$10)=$B$4,IF(AND((TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))>=$B$2),(TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))<=$B$1)),(TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))-$B$2)*24,IF((TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))>$B$1),($B$1-$B$2)*24,0)),0)+IF(INT(F$10)=$B$5,IF(AND((TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))>=$B$2),(TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))<=$B$1)),(TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))-$B$2)*24,IF((TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))>$B$1),($B$1-$B$2)*24,0)),0)+IF(INT(F$10)=$B$6,IF(AND((TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))>=$B$2),(TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))<=$B$1)),(TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))-$B$2)*24,IF((TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))>$B$1),($B$1-$B$2)*24,0)),0)+IF(AND(INT(F$10)>$B$4,INT(F$9)<$B$4),($B$1-$B$2)*24,0)+IF(AND(INT(F$10)>$B$5,INT(F$9)<$B$5),($B$1-$B$2)*24,0)+IF(AND(INT(F$10)>$B$6,INT(F$9)<$B$6),($B$1-$B$2)*24,0)

F17 =IF(INT(F$9)=$B$4,IF((TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))<$B$1),24-($B$1*24),24-(TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9)))*24)+IF((TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))>$B$2),0,($B$2*24)-(TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9)))*24),0)+IF(INT(F$9)=$B$5,IF((TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))<$B$1),24-($B$1*24),24-(TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9)))*24)+IF((TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))>$B$2),0,($B$2*24)-(TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9)))*24),0)+IF(INT(F$9)=$B$6,IF((TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))<$B$1),24-($B$1*24),24-(TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9)))*24)+IF((TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9))>$B$2),0,($B$2*24)-(TIME(HOUR(F$9),MINUTE(F$9),SECOND(F$9)))*24),0)+IF(INT(F$10)=$B$4,IF((TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))<$B$1),0,((TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10)))*24)-($B$1*24))+IF((TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))>$B$2),($B$2*24),(TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10)))*24),0)+IF(INT(F$10)=$B$5,IF((TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))<$B$1),0,((TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10)))*24)-($B$1*24))+IF((TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))>$B$2),($B$2*24),(TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10)))*24),0)+IF(INT(F$10)=$B$6,IF((TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))<$B$1),0,((TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10)))*24)-($B$1*24))+IF((TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10))>$B$2),($B$2*24),(TIME(HOUR(F$10),MINUTE(F$10),SECOND(F$10)))*24),0)+IF(AND(INT(F$10)>$B$4,INT(F$9)<$B$4),24-($B$1*24)+($B$2*24),0)+IF(AND(INT(F$10)>$B$5,INT(F$9)<$B$5),24-($B$1*24)+($B$2*24),0)+IF(AND(INT(F$10)>$B$6,INT(F$9)<$B$6),24-($B$1*24)+($B$2*24),0)

F18 =SUM(F$14:F$17)

 

[DRSteele]

Chrisdontm, thanks for you efforts...your monumental efforts. No doubt you nearly had a seizure when trying to nest all those IF statements!

I noted that the formulas for each row item are inter-dependant with the exception of Rows 16 & 17. And I noted there is one little problem in the F17 formula: there is an extra space in some of the S ECOND functions.

Other than that, it works! It provides the desired result. But this solution (like any formulaic algorithm that achieves it) is so unwieldy most people would give up trying to create an algorithm that calculates net working hours and net non-working hours over working and non-working days. I see questions such as mine all over the Forum and there seems to be no easy solution.

But there should be! MS created the NETWORKDAYS function, so there should be no reason they can't create a NETWORKHOURS function too. Please go here, sign up, vote for the creation of this type of function, and post a link to this Thread in a comment. https://excel.uservoice.com/forums/...0730205-create-networkhours-as-a-new-function

 

[DRSteele]

Oops: the desired output for Man3 was stated wrong by me. It should have been: 7, 2, 16, 7 (=32)