Parcial time calculation complex formula

[eduzs]

Hi there,

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

START	END	"NIGHT TIME" (HOURS)
04:00	23:30	2,5
14:00	01:00	3,0

<tbody>
</tbody>

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
1			"NIGHT TIME" (HOURS)
2	START	END	clock time	serial number	hours
3	4:00	23:30	2:30	0.10417	2.500
4	14:00	1:00	3:00	0.12500	3.000
5	10:00	18:00	0:00	0.00000	0.000
6	3:00	21:30	2:00	0.08333	2.000
7	12:00	6:00	7:00	0.29167	7.000
8	1:00	22:00	4:00	0.16667	4.000
9	4:59	22:01	0:02	0.00139	0.033

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula C3 =(MOD(B3-A3,1)*24-(B3<A3)*(22-5)+MEDIAN(A3*24,5,22)-MEDIAN(B3*24,5,22))/24 D3 =C3 E3 =D3*24 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

<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"