Calculate time spent in hourly interval

[phiero21]

Hi, I have Start time and End Time in excel (sometimes they can spread over days) and want to know the time spend in each hourly interval from 0 to 24.
So I want Col E to Col AB populated via a formulae and I have also entered the expected value for each cell there.
Please if anyone can help.

Also, if anyone can please explain why I have to round the log sec column. I tried (B2-A2)*24*2600 and formatted to numbers but it was giving values in decimal. Shouldn't it automatically give the right number of seconds without rounding off.

Book1
	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	AB
1					Hourly Interval
2	Start Time	End Time	Log Sec		0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
3	4/22/2024 5:05:30	4/22/2024 5:05:40	10		0	0	0	0	0	10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	4/22/2024 5:05:30	4/22/2024 6:05:40	3610		0	0	0	0	0	3270	340	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	4/22/2024 5:05:30	4/22/2024 17:10:05	43475		0	0	0	0	0	3270	3600	3600	3600	3600	3600	3600	3600	3600	3600	3600	3600	605	0	0	0	0	0	0
6	4/22/2024 22:20:30	4/23/2024 2:10:10	13780		3600	3600	610	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2370	3600
7	4/22/2024 5:05:30	4/23/2024 22:05:30	147600		3600	3600	3600	3600	3600	6870	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	3930	3600
Sheet1

Cell Formulas
Range	Formula
G2:AB2	G2	=F2+1
C3:C7	C3	=ROUND((B3-A3)*24*3600,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:AB7	Cell Value	=0	text	NO

 

[JamesCanale]

You don't say what version of excel you are running - so I did it where I could think of a way - in 365. I can't think of a good way to do it in older versions - I'd like to see it.

MrExcelPlayground22.xlsx
	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	AB
1					Hourly Interval
2	Start Time	End Time	Log Sec		0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
3	4/22/2024 5:05:30	4/22/2024 5:05:40	10		0	0	0	0	0	10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	4/22/2024 5:05:30	4/22/2024 6:05:40	3610		0	0	0	0	0	3270	340	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	4/22/2024 5:05:30	4/22/2024 17:10:05	43475		0	0	0	0	0	3270	3600	3600	3600	3600	3600	3600	3600	3600	3600	3600	3600	606	0	0	0	0	0	0
6	4/22/2024 22:20:30	4/23/2024 2:10:10	13780		3600	3600	610	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2370	3600
7	4/22/2024 5:05:30	4/23/2024 22:05:30	147600		3600	3600	3600	3600	3600	6870	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	3930	3600
Sheet7

Cell Formulas
Range	Formula
G2:AB2	G2	=F2+1
E3:AB7	E3	=LET(a,$A3,b,$B3,c,(b-a)*24*60*60+1,d,SEQUENCE(c,1,a,1/24/60/60),e,HOUR(d),f,ROWS(FILTER(e,e=E$2)),IFERROR(f,0))
C3:C7	C3	=ROUND((B3-A3)*24*3600,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:AB7	Expression	=E3=0	text	NO

As for the need to round? Time is a bit funny in excel and tiny errors pop in. (That's of little help)

 

[phiero21]

You don't say what version of excel you are running - so I did it where I could think of a way - in 365. I can't think of a good way to do it in older versions - I'd like to see it.

MrExcelPlayground22.xlsx
	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	AB
1					Hourly Interval
2	Start Time	End Time	Log Sec		0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
3	4/22/2024 5:05:30	4/22/2024 5:05:40	10		0	0	0	0	0	10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	4/22/2024 5:05:30	4/22/2024 6:05:40	3610		0	0	0	0	0	3270	340	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	4/22/2024 5:05:30	4/22/2024 17:10:05	43475		0	0	0	0	0	3270	3600	3600	3600	3600	3600	3600	3600	3600	3600	3600	3600	606	0	0	0	0	0	0
6	4/22/2024 22:20:30	4/23/2024 2:10:10	13780		3600	3600	610	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2370	3600
7	4/22/2024 5:05:30	4/23/2024 22:05:30	147600		3600	3600	3600	3600	3600	6870	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	7200	3930	3600
Sheet7

Cell Formulas
Range	Formula
G2:AB2	G2	=F2+1
E3:AB7	E3	=LET(a,$A3,b,$B3,c,(b-a)*24*60*60+1,d,SEQUENCE(c,1,a,1/24/60/60),e,HOUR(d),f,ROWS(FILTER(e,e=E$2)),IFERROR(f,0))
C3:C7	C3	=ROUND((B3-A3)*24*3600,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:AB7	Expression	=E3=0	text	NO

As for the need to round? Time is a bit funny in excel and tiny errors pop in. (That's of little help)

Thanks, exactly what I needed. Really appreciate your help