Time in each hourly interval

[phiero21]

I have a start time and end time in Col A & Vol B. Then i have 24 hourly intervals in next 24 columns. What I need is the time spent in each hourly interval

e.g.
Start Time: 3:20 am
End time: 5:10 pm


Interval:
00:00 to 01:00 00
01:00 to 02:00 00
02:00 to 03:00 00
03:00 to 04:00 40
04:00 to 05:00 60
05:00 to 06:00 10
06:00 to 07:00 00
07:00 to 08:00 00

 

[jorismoerings]

Hi,

take a look at this:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	time in	time out	00:00	01:00	02:00	03:00	04:00	05:00	06:00	07:00	08:00	09:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00	19:00	20:00	21:00	22:00	23:00	00:00
2	03:20	17:10	00:00	00:00	00:00	00:40	01:00	01:00	01:00	01:00	01:00	01:00	01:00	01:00	01:00	01:00	01:00	01:00	01:00	00:10	00:00	00:00	00:00	00:00	00:00	00:00	00:00
Sheet2

Cell Formulas
Range	Formula
C2	=IF(MEDIAN(HOUR($A2),HOUR($B2),HOUR(C$1))=HOUR(C$1),IF(C$1+TIME(1,0,0)<$B2,C$1+TIME(1,0,0)-MAX(C$1,$A2),IF(HOUR($T1)=HOUR($B2),MAX(C$1,$B2)-C$1,0)),0)

 

[phiero21]

Hi,

take a look at this:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	time in	time out	00:00	01:00	02:00	03:00	04:00	05:00	06:00	07:00	08:00	09:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00	19:00	20:00	21:00	22:00	23:00	00:00
2	03:20	17:10	00:00	00:00	00:00	00:40	01:00	01:00	01:00	01:00	01:00	01:00	01:00	01:00	01:00	01:00	01:00	01:00	01:00	00:10	00:00	00:00	00:00	00:00	00:00	00:00	00:00
Sheet2

Cell Formulas
Range	Formula
C2	=IF(MEDIAN(HOUR($A2),HOUR($B2),HOUR(C$1))=HOUR(C$1),IF(C$1+TIME(1,0,0)<$B2,C$1+TIME(1,0,0)-MAX(C$1,$A2),IF(HOUR($T1)=HOUR($B2),MAX(C$1,$B2)-C$1,0)),0)

Thanks so much for your time. It is calculating the initial intervals correctly, but not the last one. (E.g. if teh start time is 3:20 and End time is 3:30, it will show incorrectly).

 

[jorismoerings]

try this:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G	H	I
1	time in	time out	00:00	01:00	02:00	03:00	04:00	05:00	06:00
2	03:20	03:45	00:00	00:00	00:00	00:25	00:00	00:00	00:00
Sheet1

Cell Formulas
Range	Formula
C2	=IF((MEDIAN(HOUR($A2),HOUR($B2),HOUR(C$1))=HOUR(C$1)),MIN(C$1+TIME(1,0,0),$B2)-MAX($A2,C$1),0)

 

[phiero21]

try this:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G	H	I
1	time in	time out	00:00	01:00	02:00	03:00	04:00	05:00	06:00
2	03:20	03:45	00:00	00:00	00:00	00:25	00:00	00:00	00:00
Sheet1

Cell Formulas
Range	Formula
C2	=IF((MEDIAN(HOUR($A2),HOUR($B2),HOUR(C$1))=HOUR(C$1)),MIN(C$1+TIME(1,0,0),$B2)-MAX($A2,C$1),0)

Thanks, it works