Lookup date/ time in range of dates and return binary value

[Woofy_McWoof_Woof]

Hi, I'm attempting to return a binary 1 or 0 value where a look up of a date/time matches a date in string of dates. In the attached picture where the date and time (30 minute slots) matches the time that falls within that 30 minutes then it returns a value of 1, otherwise zero.

The date and time could contain seconds as well butstill falls within the 30 minutes and should be counted.

I've tried few things but it doesn't seem to work, any ideas? Thanks.

 

[StuLux]

Try this (test with some different times and dates)

Book2
	A	B	C	D	E
1	Start	End		Date	Return Value
2	01/05/2022 19:02:49	01/05/2022 19:39:49		01/05/2022 16:00	0
3				01/05/2022 16:30	0
4				01/05/2022 17:00	0
5				01/05/2022 17:30	0
6				01/05/2022 18:00	0
7				01/05/2022 18:30	0
8				01/05/2022 19:00	1
9				01/05/2022 19:30	1
10				01/05/2022 20:00	0
11				01/05/2022 20:30	0
12				01/05/2022 21:00	0
13				01/05/2022 21:30	0
14				01/05/2022 22:00	0
Sheet1

Cell Formulas
Range	Formula
E2:E14	E2	=IF(AND($A$2>D2,$A$2<D3),1,IF(AND($B$2>D2,$B$2<D3),1,0))

 

[Woofy_McWoof_Woof]

Try this (test with some different times and dates)

Book2
	A	B	C	D	E
1	Start	End		Date	Return Value
2	01/05/2022 19:02:49	01/05/2022 19:39:49		01/05/2022 16:00	0
3				01/05/2022 16:30	0
4				01/05/2022 17:00	0
5				01/05/2022 17:30	0
6				01/05/2022 18:00	0
7				01/05/2022 18:30	0
8				01/05/2022 19:00	1
9				01/05/2022 19:30	1
10				01/05/2022 20:00	0
11				01/05/2022 20:30	0
12				01/05/2022 21:00	0
13				01/05/2022 21:30	0
14				01/05/2022 22:00	0
Sheet1

Cell Formulas
Range	Formula
E2:E14	E2	=IF(AND($A$2>D2,$A$2<D3),1,IF(AND($B$2>D2,$B$2<D3),1,0))

Thank you, that worked perfectly. I should have mentioned (sorry) that I may have multiple references to search from. If I lock in the A2 and B2, how could I reference the data underneath?

 

[StuLux]

Could you clarify - would you want the return value to be a count of the number of times that fall in that slot rather than just a 1

 

[Woofy_McWoof_Woof]

Could you clarify - would you want the return value to be a count of the number of times that fall in that slot rather than just a 1

I'd like to drag the formula down in Column E for the whole year and return a value of 1 where the times on the left falls within that half hour. It's pretty much the same as what is is now but increasing the range on the left rather than the just the one line at the monent.

 

[StuLux]

I've added in a couple of helper columns that lookup the start and end times and find the closest match from the table of 1/2 hour slots. Columns H & I then provide a count of Start and End tiems for each slot - does this give you something you could work with?

Book2
	A	B	C	D	E	F	G	H	I
1	Start	End		Start Lookup	End Lookup		Date	Count of Start	Count of End
2	01/05/2022 01:42:20	01/05/2022 02:29:20		01/05/2022 01:30	01/05/2022 02:00		01/05/2022 00:00	0	0
3	01/05/2022 03:32:14	01/05/2022 04:15:18		01/05/2022 03:30	01/05/2022 04:00		01/05/2022 00:30	0	0
4	01/05/2022 05:05:11	01/05/2022 05:25:14		01/05/2022 05:00	01/05/2022 05:00		01/05/2022 01:00	0	0
5	01/05/2022 07:15:19	01/05/2022 07:58:45		01/05/2022 07:00	01/05/2022 07:30		01/05/2022 01:30	1	0
6	01/05/2022 08:17:18	01/05/2022 09:25:41		01/05/2022 08:00	01/05/2022 09:00		01/05/2022 02:00	0	1
7							01/05/2022 02:30	0	0
8							01/05/2022 03:00	0	0
9							01/05/2022 03:30	1	0
10							01/05/2022 04:00	0	1
11							01/05/2022 04:30	0	0
12							01/05/2022 05:00	1	1
13							01/05/2022 05:30	0	0
14							01/05/2022 06:00	0	0
15							01/05/2022 06:30	0	0
16							01/05/2022 07:00	1	0
17							01/05/2022 07:30	0	1
18							01/05/2022 08:00	1	0
19							01/05/2022 08:30	0	0
20							01/05/2022 09:00	0	1
21							01/05/2022 09:30	0	0
22							01/05/2022 10:00	0	0
Sheet1

Cell Formulas
Range	Formula
D2:E6	D2	=VLOOKUP(A2,$G$2:$G$19337,1,TRUE)
H2:H22	H2	=COUNTIF($D$2:$D$24,G2)
I2:I22	I2	=COUNTIF($E$2:$E$24,G2)

 

[Woofy_McWoof_Woof]

I've added in a couple of helper columns that lookup the start and end times and find the closest match from the table of 1/2 hour slots. Columns H & I then provide a count of Start and End tiems for each slot - does this give you something you could work with?

Book2
	A	B	C	D	E	F	G	H	I
1	Start	End		Start Lookup	End Lookup		Date	Count of Start	Count of End
2	01/05/2022 01:42:20	01/05/2022 02:29:20		01/05/2022 01:30	01/05/2022 02:00		01/05/2022 00:00	0	0
3	01/05/2022 03:32:14	01/05/2022 04:15:18		01/05/2022 03:30	01/05/2022 04:00		01/05/2022 00:30	0	0
4	01/05/2022 05:05:11	01/05/2022 05:25:14		01/05/2022 05:00	01/05/2022 05:00		01/05/2022 01:00	0	0
5	01/05/2022 07:15:19	01/05/2022 07:58:45		01/05/2022 07:00	01/05/2022 07:30		01/05/2022 01:30	1	0
6	01/05/2022 08:17:18	01/05/2022 09:25:41		01/05/2022 08:00	01/05/2022 09:00		01/05/2022 02:00	0	1
7							01/05/2022 02:30	0	0
8							01/05/2022 03:00	0	0
9							01/05/2022 03:30	1	0
10							01/05/2022 04:00	0	1
11							01/05/2022 04:30	0	0
12							01/05/2022 05:00	1	1
13							01/05/2022 05:30	0	0
14							01/05/2022 06:00	0	0
15							01/05/2022 06:30	0	0
16							01/05/2022 07:00	1	0
17							01/05/2022 07:30	0	1
18							01/05/2022 08:00	1	0
19							01/05/2022 08:30	0	0
20							01/05/2022 09:00	0	1
21							01/05/2022 09:30	0	0
22							01/05/2022 10:00	0	0
Sheet1

Cell Formulas
Range	Formula
D2:E6	D2	=VLOOKUP(A2,$G$2:$G$19337,1,TRUE)
H2:H22	H2	=COUNTIF($D$2:$D$24,G2)
I2:I22	I2	=COUNTIF($E$2:$E$24,G2)

Thank you, it worked though for cell E2 would this be = VLOOKUP(B2,$G$2:$G$19337,1,TRUE) ?

 

[StuLux]

Thank you, it worked though for cell E2 would this be = VLOOKUP(B2,$G$2:$G$19337,1,TRUE) ?

Yes, not sure why XL2BB didn't show that as well.

 

[Woofy_McWoof_Woof]

Good stuff, thanks very much for your help.

 

[Woofy_McWoof_Woof]

Sorry, I've noticed one thing. If the Start and end time covers several hours, then it only counts the first and last half hour as 1 with the other half hours returning zero. Is there a way of ensuring that all the half hours within those hours returns 1 rather than zero?