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:
</end;>
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:
HTML:
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