Hour of the Day Between Two Time Periods

[CJ_15]

Hello - I am looking to determine which hours of the day (not total) were worked between two time periods. In the below example, first row, I am looking for a formula to drag across the hours of the day based on clock in and clock out times, rounded to the nearest quarter of an hour (expressed where 15 mins = 0.25). I manually entered the data shown in yellow/red columns to show what I am looking for the end result to be. I thought the FLOOR function with a nested IF would help me, but I'm having trouble with the logic. Thank you very much for any assistance you can provide.

Text form of above screen shot*

STARTDTM.2

ENDDTM.1

ENDDTM.2

Worked Time Hours

12 AM - 1 AM

1 AM - 2 AM

2 AM - 3 AM

3 AM - 4 AM

4 AM - 5 AM

5 AM - 6 AM

6 AM - 7 AM

7 AM - 8 AM

8 AM - 9 AM

9 AM - 10 AM

10 AM - 11 AM

5:42 PM​

7/1/2020​

10:12 PM​

4.5​

0.25​

1​

1​

1​

1​

0.25​

10:42 PM​

7/1/2020​

11:00 PM​

0.3​

0.3​

11:00 PM​

7/2/2020​

12:00 AM​

1​

12:00 AM​

7/2/2020​

6:00 AM​

6​

1​

1​

1​

1​

1​

1​

 

[maabadi]

Welcome to MrExcel MessageBoard
if you want to See minutes also you should change Number format at Data that input times.
1. I changed first column to Start time of each column then:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	STARTDTM.2	ENDDTM.1	ENDDTM.2	Worked Time Hours	12:00:00 AM	1:00:00 AM	2:00:00 AM	3:00:00 AM	4:00:00 AM	5:00:00 AM	6:00:00 AM	7:00:00 AM	8:00:00 AM	9:00:00 AM	10:00:00 AM
2	5:42 PM	7/1/2020	10:12 PM	4.5						0.25	1	1	1	1	0.25
3	10:42 PM	7/1/2020	11:00 PM	0.3											0.3
4	11:00 PM	7/2/2020	12:00 AM	0
5	12:00 AM	7/2/2020	6:00 AM	6	1	1	1	1	1	1
6
7				Criteria 1	Criteria 2	Sumifs
8			For Row2	7:00:00 AM	10:00:00 AM	3.25
9			For Total	7:00:00 AM	10:00:00 AM	3.55
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=SUM(E2:O2)
D8:D9	D8	=$L$1
E8:E9	E8	=$O$1
F8	F8	=SUMIFS($E$2:$O$2,$E$1:$O$1,">="&$L$1,$E$1:$O$1,"<"&$O$1+1/24)
F9	F9	=SUMPRODUCT(($E$2:$O$5)*($E$1:$O$1>=D9)*($E$1:$O$1<(E9+1/24)))