Time Calc: Hours shared between intervals

[DMurray3]

Dear all,

For time-sheet calculation purposes, need to determine the number of hours shared between two time intervals; in this new case, I need to correctly determine the values in cells H17 & H18.

Some background:

I need to calculate multiple hours a shift 'shares' with multiple 'Periods of Interest'; I have managed to calculate some of these, but I am stuck with Number of Hours the Night-Shift has with the Period of Interest in Row 5.

The basic formula I am using to get the hour shared between intervals is: <end; min(end;="" upper)-max(start,="" lower);="" max(0;upper-start)+max(0;end-lower)).
IF(start<end; MIN(end; upper)-MAX(start, lower); MAX(0;upper-start)+MAX(0;end-lower))
This works OK, when the 'Periods of Interest' do not cross the 'day' boundry; but in the current case, the 'period of interest' of the Night-time hours (row 5: from 19:00 to 06:00) do in fact cross the day boundry.

The following sample clearly describe the definitions/parameters I am using; further in 'col M', I have included what the correct results for each of the sample time rows are to be.

I would appreciate some guidance as to how correctly calculate the Number of Hours the Night-Shift has with the Period of Interest in Row 5, for the two highlighted cells (rows 17 & 18).

I wanted my question to be as clear as possible and the I trust the example provided is sufficient.

Thank you in advance for your help... Kind regards, DMurray3

My sample:

ABCDEFGHIJKLM1Periods of Interest2LOWERUPPERcommentys3Daytime hours (DAYT)6:0019:00Shift hour in this period is paid 1,0 x hourly wage4Single Over-Time (SOT)6:000:00PI 2OT hours falling in this range are paid 1.5 x hourly wage5Night-time hours (NGHT)19:006:00PI 3cross the day boundaryShift hour in this period is paid 1,25 x hourly wage6Double Over-Time (DOT)0:006:00PI 4cross the day boundaryOT hours falling in this range are paid 2 x hourly wage7Shift length (# hours)8,00OT -either Single or Double- is calculated after this number of regular hours + meal break.89duration=MOD(END-START;1)10included=IF(start<end; MIN(end; upper)-MAX(start; lower); MAX(0;upper-start)+MAX(0;end-lower))111213JFNNSFMANUALLY CALCULATED14shift typeSTARTENDmeal breakhrs_totalhrs_OT_totOT Starts Herehrs_NGHThrs_SOThrs_DOTOT Chk15D08:0001:001,0017,008,0017:000,00 7,00 1,00 0,00 SOT = From "OT Stars Here" (17h00) to "END" (01h00) there are 7 hours of OT; None for DOT or NGHT16D08:0023:001,0015,006,0017:000,00 6,00 0,00 -0,00 SOT = From "OT Stars Here" (17h00) to "END" (23h00) there are 6 hours of OT; None for DOT or NGHT17N18:0008:001,0014,005,0003:000,00 2,00 3,00 0,00 NGHT = 7 hours between 19h00 - 032h00 MEAL BREAK is substracted (as per PI 3); DOT = 3 (OT between Midnight & 06h00 as per PI 4); SOT = 2 (OT between 06h00 and Midnight as per PI 2)18N20:0007:001,0011,002,0005:000,00 1,00 1,00 0,00 NGHT = 8 hours from 20h00 thru 04h00 MEAL BREAK is substracted (as per PI 3); DOT = 1 (OT between Midnight & 06h00 as per PI 4); SOT = 1 (OT between 06h00 and Midnight as per PI 2)[CENTER][COLOR=#161120][B]Hoja1[/B][/COLOR][/CENTER]

Worksheet FormulasCellFormulaE15=+MOD(C15-B15,1)*24F15=+E15-D15-$C$7G15=+B15+(D15/24)+($C$7/24)H15=+IF(A15="D",0,IF(B15<C15,MIN(C15,$D$5)-MAX(B15,$C$5),MAX(0,$D$5-B15)+MAX(0,C15-$C$5)))*24I15=+IF(G15<C15,MIN(C15,$D$4)-MAX(G15,$C$4),MAX(0,$D$4-G15)+MAX(0,C15-$C$4))*24J15=+F15-I15K15=+F15-I15-J15E16=+MOD(C16-B16,1)*24F16=+E16-D16-$C$7G16=+B16+(D16/24)+($C$7/24)H16=+IF(A16="D",0,IF(B16<C16,MIN(C16,$D$5)-MAX(B16,$C$5),MAX(0,$D$5-B16)+MAX(0,C16-$C$5)))*24I16=+IF(G16<C16,MIN(C16,$D$4)-MAX(G16,$C$4),MAX(0,$D$4-G16)+MAX(0,C16-$C$4))*24J16=+F16-I16K16=+F16-I16-J16E17=+MOD(C17-B17,1)*24F17=+E17-D17-$C$7G17=+B17+(D17/24)+($C$7/24)H17=+IF(A17="D",0,IF(B17<C17,MIN(C17,$D$5)-MAX(B17,$C$5),MAX(0,$D$5-B17)+MAX(0,C17-$C$5)))*24I17=+IF(G17<C17,MIN(C17,$D$4)-MAX(G17,$C$4),MAX(0,$D$4-G17)+MAX(0,C17-$C$4))*24J17=+F17-I17K17=+F17-I17-J17E18=+MOD(C18-B18,1)*24F18=+E18-D18-$C$7G18=+B18+(D18/24)+($C$7/24)H18=+IF(A18="D",0,IF(B18<C18,MIN(C18,$D$5)-MAX(B18,$C$5),MAX(0,$D$5-B18)+MAX(0,C18-$C$5)))*24I18=+IF(G18<C18,MIN(C18,$D$4)-MAX(G18,$C$4),MAX(0,$D$4-G18)+MAX(0,C18-$C$4))*24J18=+F18-I18K18=+F18-I18-J18

</end;>

 

[DMurray3]

For unkwonn reasons, what I included in my original thread was supposed to the html image of my worksheet which I created with HtmlMaker (which I obtained from this forum/website)..

This is the correct html image of the same file... I apologize:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1			Periods of Interest
2			LOWER	UPPER			commentys
3	Daytime hours (DAYT)		6:00	19:00			Shift hour in this period is paid 1,0 x hourly wage
4	Single Over-Time (SOT)		6:00	0:00	PI 2		OT hours falling in this range are paid 1.5 x hourly wage
5	Night-time hours (NGHT)		19:00	6:00	PI 3	cross the day boundary	Shift hour in this period is paid 1,25 x hourly wage
6	Double Over-Time (DOT)		0:00	6:00	PI 4	cross the day boundary	OT hours falling in this range are paid 2 x hourly wage
7	Shift length (# hours)		8,00				OT -either Single or Double- is calculated after this number of regular hours + meal break.
8
9	duration=	MOD(END-START;1)
10	included=	IF(start<end; MIN(end; upper)-MAX(start; lower); MAX(0;upper-start)+MAX(0;end-lower))
11
12
13			JFN				NSF						MANUALLY CALCULATED
14	shift type	START	END	meal break	hrs_total	hrs_OT_tot	OT Starts Here	hrs_NGHT	hrs_SOT	hrs_DOT	OT Chk
15	D	08:00	01:00	1,00	17,00	8,00	17:00	0,00	7,00	1,00	0,00		SOT = From "OT Stars Here" (17h00) to "END" (01h00) there are 7 hours of OT; None for DOT or NGHT
16	D	08:00	23:00	1,00	15,00	6,00	17:00	0,00	6,00	0,00	-0,00		SOT = From "OT Stars Here" (17h00) to "END" (23h00) there are 6 hours of OT; None for DOT or NGHT
17	N	18:00	08:00	1,00	14,00	5,00	03:00	0,00	2,00	3,00	0,00		NGHT = 7 hours between 19h00 - 032h00 MEAL BREAK is substracted (as per PI 3); DOT = 3 (OT between Midnight & 06h00 as per PI 4); SOT = 2 (OT between 06h00 and Midnight as per PI 2)
18	N	20:00	07:00	1,00	11,00	2,00	05:00	0,00	1,00	1,00	0,00		NGHT = 8 hours from 20h00 thru 04h00 MEAL BREAK is substracted (as per PI 3); DOT = 1 (OT between Midnight & 06h00 as per PI 4); SOT = 1 (OT between 06h00 and Midnight as per PI 2)

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

Hoja1​

Worksheet Formulas<style="text-align:left">=+F18-I18-J18</style="text-align:left"> Cell Formula E15 =+MOD(C15-B15,1)*24 F15 =+E15-D15-$C$7 G15 =+B15+(D15/24)+($C$7/24) H15 =+IF(A15="D",0,IF(B15<C15,MIN(C15,$D$5)-MAX(B15,$C$5),MAX(0,$D$5-B15)+MAX(0,C15-$C$5)))*24 I15 =+IF(G15<C15,MIN(C15,$D$4)-MAX(G15,$C$4),MAX(0,$D$4-G15)+MAX(0,C15-$C$4))*24 J15 =+F15-I15 K15 =+F15-I15-J15 E16 =+MOD(C16-B16,1)*24 F16 =+E16-D16-$C$7 G16 =+B16+(D16/24)+($C$7/24) H16 =+IF(A16="D",0,IF(B16<C16,MIN(C16,$D$5)-MAX(B16,$C$5),MAX(0,$D$5-B16)+MAX(0,C16-$C$5)))*24 I16 =+IF(G16<C16,MIN(C16,$D$4)-MAX(G16,$C$4),MAX(0,$D$4-G16)+MAX(0,C16-$C$4))*24 J16 =+F16-I16 K16 =+F16-I16-J16 E17 =+MOD(C17-B17,1)*24 F17 =+E17-D17-$C$7 G17 =+B17+(D17/24)+($C$7/24) H17 =+IF(A17="D",0,IF(B17<C17,MIN(C17,$D$5)-MAX(B17,$C$5),MAX(0,$D$5-B17)+MAX(0,C17-$C$5)))*24 I17 =+IF(G17<C17,MIN(C17,$D$4)-MAX(G17,$C$4),MAX(0,$D$4-G17)+MAX(0,C17-$C$4))*24 J17 =+F17-I17 K17 =+F17-I17-J17 E18 =+MOD(C18-B18,1)*24 F18 =+E18-D18-$C$7 G18 =+B18+(D18/24)+($C$7/24) H18 =+IF(A18="D",0,IF(B18<C18,MIN(C18,$D$5)-MAX(B18,$C$5),MAX(0,$D$5-B18)+MAX(0,C18-$C$5)))*24 I18 =+IF(G18<C18,MIN(C18,$D$4)-MAX(G18,$C$4),MAX(0,$D$4-G18)+MAX(0,C18-$C$4))*24 J18 =+F18-I18 K18 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

td