Parcial time calculation complex formula

[eduzs]

Hi there,

Given a time (fist column START, second column END:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]START[/TD]
[TD]END[/TD]
[TD]"NIGHT TIME" (HOURS)[/TD]
[/TR]
[TR]
[TD]04:00[/TD]
[TD]23:30[/TD]
[TD]2,5[/TD]
[/TR]
[TR]
[TD]14:00[/TD]
[TD]01:00[/TD]
[TD]3,0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to get the "NIGHT TIME", that is the time not between 05:00 and 22, or, in other words, thats from 0:00 to 5:00, and from 22:00 to 24:00.

In the first example the the formula must return 2,5 hours (1 hour from 4:00 to 5:00 + 1,5 hour from 22:00 to 23:30.

In the second excample the formula must return 3,0 hours (from 22:00 to 01:00).

This is possible by formula?

Need help.

Thanks.

 

[DRSteele]

I am working on it for you.

 

[DRSteele]

I hope this works. Clock maths is difficult in Excel. I found the answer in this post from years ago; see thread comment #10 http://www.mrexcel.com/forum/excel-...-night-shift-hours-date-time.html#post3847267

	A	B	C	D	E
START	END	clock time	serial number	hours

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]"NIGHT TIME" (HOURS)[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]

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

[TD="align: center"]3[/TD]
[TD="align: right"]4:00[/TD]
[TD="align: right"]23:30[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2:30[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0.10417[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2.500[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]1:00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]3:00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0.12500[/TD]
[TD="bgcolor: #E2EFDA, align: right"]3.000[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]18:00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0:00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0.00000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0.000[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3:00[/TD]
[TD="align: right"]21:30[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2:00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0.08333[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2.000[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]6:00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]7:00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0.29167[/TD]
[TD="bgcolor: #E2EFDA, align: right"]7.000[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"]22:00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]4:00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0.16667[/TD]
[TD="bgcolor: #E2EFDA, align: right"]4.000[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]4:59[/TD]
[TD="align: right"]22:01[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0:02[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0.00139[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0.033[/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]=(MOD(B3-A3,1)*24-(B3<A3)*(22-5)+MEDIAN(A3*24,5,22)-MEDIAN(B3*24,5,22))/24[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=C3[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=D3*24[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

<strike></strike>

 

[eduzs]

I was unable to solve the question with the above formula, but I found the solution exactly where you told! Great formula in #10, works fine.

For "Night Time" <a3)*(22-5)+med(a3*24;5;22)-med(b3*24;5;22)) 24
=(MOD(B3-A3;1)*24-(B3 < A3)*(22-5)+MED(A3*24;5;22)-MED(B3*24;5;22))/24
<a3)*(22-5)+median(a3*24,5,22)-median(b3*24,5,22)) 24[="" code]
<a3)*(22-5)+median(a3*24,5,22)-median(b3*24,5,22)) 24[="" code]
<a3)*(22-5)+median(a3*24,5,22)-median(b3*24,5,22)) 24
For "Day time" =MOD(B3-A3;1)-C3

Thanks a lot!.</a3)*(22-5)+median(a3*24,5,22)-median(b3*24,5,22))></a3)*(22-5)+median(a3*24,5,22)-median(b3*24,5,22))></a3)*(22-5)+median(a3*24,5,22)-median(b3*24,5,22))></a3)*(22-5)+med(a3*24;5;22)-med(b3*24;5;22))>

 

[eduzs]

Ps.: remove blank space between B3 < A3, also replace ; with ,
MED = MEDIAN

Now I'm think about a way to show in "hours" not in time.

Found easily, just removed "/24"