Time Calc: Hours shared between intervals

D

DMurray3

New Member
Joined
Dec 23, 2010
Messages
26
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:

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

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
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:

ABCDEFGHIJKLM
Daytime hours (DAYT)Shift hour in this period is paid 1,0 x hourly wage
Single Over-Time (SOT)PI 2OT hours falling in this range are paid 1.5 x hourly wage
Night-time hours (NGHT)PI 3cross the day boundaryShift hour in this period is paid 1,25 x hourly wage
Double Over-Time (DOT)PI 4cross the day boundaryOT hours falling in this range are paid 2 x hourly wage
Shift length (# hours)OT -either Single or Double- is calculated after this number of regular hours + meal break.
duration=MOD(END-START;1)
included=IF(start<end; MIN(end; upper)-MAX(start; lower); MAX(0;upper-start)+MAX(0;end-lower))
MANUALLY CALCULATED
SOT = From "OT Stars Here" (17h00) to "END" (01h00) there are 7 hours of OT; None for DOT or NGHT
SOT = From "OT Stars Here" (17h00) to "END" (23h00) there are 6 hours of OT; None for DOT or NGHT
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)
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>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Periods of Interest[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: center"]LOWER[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: center"]UPPER[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]commentys[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: center"]6:00[/TD]
[TD="align: center"]19:00[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: center"]6:00[/TD]
[TD="align: center"]0:00[/TD]

[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: center"]19:00[/TD]
[TD="align: center"]6:00[/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: center"]0:00[/TD]
[TD="align: center"]6:00[/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: center"]8,00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]JFN[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]NSF[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]shift type[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]START[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]END[/TD]
[TD="align: center"]meal break[/TD]
[TD="align: center"]hrs_total[/TD]
[TD="align: center"]hrs_OT_tot[/TD]
[TD="align: center"]OT Starts Here[/TD]
[TD="align: center"]hrs_NGHT[/TD]
[TD="align: center"]hrs_SOT[/TD]
[TD="align: center"]hrs_DOT[/TD]
[TD="align: center"]OT Chk[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]08:00[/TD]
[TD="align: center"]01:00[/TD]
[TD="align: center"]1,00[/TD]
[TD="align: center"]17,00[/TD]
[TD="align: center"]8,00[/TD]
[TD="align: center"]17:00[/TD]
[TD="align: center"]0,00 [/TD]
[TD="align: center"]7,00 [/TD]
[TD="align: center"]1,00 [/TD]
[TD="align: center"]0,00 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]08:00[/TD]
[TD="align: center"]23:00[/TD]
[TD="align: center"]1,00[/TD]
[TD="align: center"]15,00[/TD]
[TD="align: center"]6,00[/TD]
[TD="align: center"]17:00[/TD]
[TD="align: center"]0,00 [/TD]
[TD="align: center"]6,00 [/TD]
[TD="align: center"]0,00 [/TD]
[TD="align: center"]-0,00 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]18:00[/TD]
[TD="align: center"]08:00[/TD]
[TD="align: center"]1,00[/TD]
[TD="align: center"]14,00[/TD]
[TD="align: center"]5,00[/TD]
[TD="align: center"]03:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]0,00 [/TD]
[TD="align: center"]2,00 [/TD]
[TD="align: center"]3,00 [/TD]
[TD="align: center"]0,00 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]20:00[/TD]
[TD="align: center"]07:00[/TD]
[TD="align: center"]1,00[/TD]
[TD="align: center"]11,00[/TD]
[TD="align: center"]2,00[/TD]
[TD="align: center"]05:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]0,00 [/TD]
[TD="align: center"]1,00 [/TD]
[TD="align: center"]1,00 [/TD]
[TD="align: center"]0,00 [/TD]
[TD="align: right"][/TD]

</tbody>
Hoja1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas<style="text-align:left">=+F18-I18-J18</style="text-align:left">[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E15[/TH]
[TD="align: left"]=+MOD(C15-B15,1)*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F15[/TH]
[TD="align: left"]=+E15-D15-$C$7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G15[/TH]
[TD="align: left"]=+B15+(D15/24)+($C$7/24)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H15[/TH]
[TD="align: left"]=+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[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I15[/TH]
[TD="align: left"]=+IF(G15<C15,MIN(C15,$D$4)-MAX(G15,$C$4),MAX(0,$D$4-G15)+MAX(0,C15-$C$4))*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J15[/TH]
[TD="align: left"]=+F15-I15[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K15[/TH]
[TD="align: left"]=+F15-I15-J15[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E16[/TH]
[TD="align: left"]=+MOD(C16-B16,1)*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F16[/TH]
[TD="align: left"]=+E16-D16-$C$7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G16[/TH]
[TD="align: left"]=+B16+(D16/24)+($C$7/24)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H16[/TH]
[TD="align: left"]=+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[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I16[/TH]
[TD="align: left"]=+IF(G16<C16,MIN(C16,$D$4)-MAX(G16,$C$4),MAX(0,$D$4-G16)+MAX(0,C16-$C$4))*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J16[/TH]
[TD="align: left"]=+F16-I16[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K16[/TH]
[TD="align: left"]=+F16-I16-J16[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E17[/TH]
[TD="align: left"]=+MOD(C17-B17,1)*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F17[/TH]
[TD="align: left"]=+E17-D17-$C$7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G17[/TH]
[TD="align: left"]=+B17+(D17/24)+($C$7/24)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H17[/TH]
[TD="align: left"]=+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[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I17[/TH]
[TD="align: left"]=+IF(G17<C17,MIN(C17,$D$4)-MAX(G17,$C$4),MAX(0,$D$4-G17)+MAX(0,C17-$C$4))*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J17[/TH]
[TD="align: left"]=+F17-I17[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K17[/TH]
[TD="align: left"]=+F17-I17-J17[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E18[/TH]
[TD="align: left"]=+MOD(C18-B18,1)*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F18[/TH]
[TD="align: left"]=+E18-D18-$C$7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G18[/TH]
[TD="align: left"]=+B18+(D18/24)+($C$7/24)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H18[/TH]
[TD="align: left"]=+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[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I18[/TH]
[TD="align: left"]=+IF(G18<C18,MIN(C18,$D$4)-MAX(G18,$C$4),MAX(0,$D$4-G18)+MAX(0,C18-$C$4))*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J18[/TH]
[TD="align: left"]=+F18-I18[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K18[/TH]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


td
 
Upvote 0
You must log in or register to reply here.

Similar threads

esset
  • Solved
Splitting hours worked into multiple shift patterns
Replies
3
Views
780
esset
esset
P
  • Solved
Calculate time spent in hourly interval
Replies
2
Views
219
phiero21
P
S
  • Question Question
How do I store a battery's state of charge past midnight?
Replies
3
Views
247
NdNoviceHlp
NdNoviceHlp
R
  • Solved
How to independently sort multiple columns of this SEQUENCE output without effecting the positions of the columns and of the blanks?
2
Replies
15
Views
519
Rnkhch
R
N
  • Solved
Count days between to Table Ranges that falls in SLA intervals
Replies
4
Views
122
Nicha
N

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top