How to calculate times between a range with two conditions

[amusaber]

How to calculate times between a range with two conditions?

times 01.xlsx
	A	B	C	D	E
1	start time	end time	how much times in range (00:00 - 07:00)	how much times in range (20:00 - 23:59)	how much times not in these two ranges
2	3:00	21:00	4:00	1:00	13:00
3			*formula1	*formula2	*formula3
4
5	*I need formulas for yellow parts
Sheet1

 

[earthworm]

Try this

Book1
	A	B	C	D	E
1	start time	end time	how much times in range (00:00 - 07:00)	how much times in range (20:00 - 23:59)	how much times not in these two ranges
2	03:00	21:00	04:00	01:00	13:00
3			*formula1	*formula2	*formula3
Sheet1

Cell Formulas
Range	Formula
C2	C2	=(SUMPRODUCT(--(ROW(INDIRECT(1&":"&7))>=A2*24)*(ROW(INDIRECT(1&":"&7))<7)))/24
D2	D2	=SUMPRODUCT(--(ROW(INDIRECT(B2*24&":"&24))>=A2*24)*(ROW(INDIRECT(B2*24&":"&24))<=B2*24))/24
E2	E2	=(SUMPRODUCT(--(ROW(INDIRECT(1&":"&24))>=A2*24)*(ROW(INDIRECT(1&":"&24))<=B2*24))-(C2*24)-(D2*24)-1)/24

Last part was confusing so i just tried a fluke

 

[amusaber]

Try this

Book1
	A	B	C	D	E
1	start time	end time	how much times in range (00:00 - 07:00)	how much times in range (20:00 - 23:59)	how much times not in these two ranges
2	03:00	21:00	04:00	01:00	13:00
3			*formula1	*formula2	*formula3
Sheet1

Cell Formulas
Range	Formula
C2	C2	=(SUMPRODUCT(--(ROW(INDIRECT(1&":"&7))>=A2*24)*(ROW(INDIRECT(1&":"&7))<7)))/24
D2	D2	=SUMPRODUCT(--(ROW(INDIRECT(B2*24&":"&24))>=A2*24)*(ROW(INDIRECT(B2*24&":"&24))<=B2*24))/24
E2	E2	=(SUMPRODUCT(--(ROW(INDIRECT(1&":"&24))>=A2*24)*(ROW(INDIRECT(1&":"&24))<=B2*24))-(C2*24)-(D2*24)-1)/24

Last part was confusing so i just tried a fluke

thank you alot, but its too confusing for me to understand

 

[Dave Patton]

Time 2023.xlsm
	A	B	C	D	E
1	start time	end time	how much times in range (00:00 - 07:00)	how much times in range (20:00 - 23:59)	how much times not in these two ranges
2	3:00	21:00	4:00	1:00	13:00
2cc

Cell Formulas
Range	Formula
C2	C2	=MIN(7/24,B2)-A2
D2	D2	=MIN(0.99999,B2)-MAX(A2,20/24)
E2	E2	=B2-A2-C2-D2

 

[Dave Patton]

Time 2023.xlsm
	A	B				F	G
1	start time	end time				Total	Regular
2	3:00	21:00				18:00	13:00
2cc

Cell Formulas
Range	Formula
F2	F2	=B2-A2
G2	G2	=MIN(20/24,B2)-MAX(7/24,A3)

 

[kvsrinivasamurthy]

In C2

=MEDIAN(0,TIMEVALUE("7:00:00"),$B2)-MEDIAN(0,TIMEVALUE("7:00:00"),$A2)

In D2

=MEDIAN(TIMEVALUE("20:00:00"),1,$B2)-MEDIAN(TIMEVALUE("20:00:00"),1,$A2)

In E2

=$B2-$A2-$C2-$D2