Hi. I'm trying to fill 1 for the time between arrival and departure.

[AliaNi]

I tried different formulas but they either work for some cases or don't work at all.

I entered the data in the first row manually.

Arrv Hour​

Dept Hour​

12:00 AM​

1:00 AM​

2:00 AM​

3:00 AM​

4:00 AM​

5:00 AM​

6:00 AM​

7:00 AM​

8:00 AM​

9:00 AM​

10:00 AM​

11:00 AM​

12:00 PM​

1:00 PM​

2:00 PM​

3:00 PM​

4:00 PM​

5:00 PM​

6:00 PM​

7:00 PM​

8:00 PM​

9:00 PM​

10:00 PM​

11:00 PM​

12:00 AM​

5:00 AM​

1

1

1

1

1

1

12:00 AM​

12:00 PM​

12:00 AM​

5:00 AM​

12:00 AM​

5:00 AM​

 

[Asbestos_Jen]

When I used the formula you have with the data you pasted, I got incorrect results. Check that your times listed in row 1 and columns F and G are formatted as times. I used 24 hr time below and the formula worked.

2024-06-20.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	Arrv Hour	Dept Hour	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
2	00:00	05:00	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
3	00:00	12:00	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0
4	00:00	05:00	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	00:00	05:00	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
H2:AE5	H2	=IF(AND($F2<=H$1,$G2>=H$1),1,0)