Pick the first value from a range of data which is more than X value

[gnaga]

Book1
	E	F	G	H	I
6
7		10:00:00 AM
8
9
10
11
12		10:00:00 AM
13		10:06:00 AM
14		10:06:00 AM
15		10:12:00 AM
16		10:12:00 AM
17		10:18:00 AM
18		10:18:00 AM
19		10:24:00 AM
20		10:24:00 AM
21		10:27:00 AM
22		10:27:00 AM
23		10:30:00 AM
24		10:30:00 AM
25		10:42:00 AM
26		10:42:00 AM
27		10:54:00 AM
28
29
30
31
32		10:54:00 AM
33		11:00:00 AM
34		11:00:00 AM
35		11:06:00 AM
36
Sheet1

Cell Formulas
Range	Formula
H21	H21	=INDEX(F8:F35,MATCH(TRUE,INDEX(F8:F35>(F7+(60/1440)),0),0))

Formula in the cell H21 returns blank cell. I wish to pick the time which is just crossing more than an hour than "F7". The correct result is "F35" should return. I am finding it difficult to get this correct. Please help.

 

[Phuoc]

Try this:

=INDEX(F8:F35,MATCH(1,INDEX((F8:F35>F7+(60/1440))*(F8:F35<=1440),0),0))

 

[gnaga]

Thanks Phuoc??. It works fine.

 

[Peter_SSs]

If your times are always in ascending order like your sample, then another way would be

=AGGREGATE(15,6,F8:F35/(F8:F35>F7+1/24),1)

 

[gnaga]

Thanks Peter_SSs,

Your solution is also working fine. In fact I would like to highlight the hour which is crossing one hour from the specific time (Row 5) as I given in my example. Could you please give some guidance how to build formula for conditional formatting.

Book1
	E	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	AF
5	Sun				Mon				Tue				Wed				Thu				Fri				Sat
6	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00
7				22:00				22:00				22:00				22:00				22:00				22:00				22:00
8				22:09				22:12				22:03				22:03				22:03				22:06				22:03
9								22:12
10								22:15
11		10:00		22:09		10:00		22:15		10:00		22:03		10:00		22:03		10:00		22:03		10:00	16:00	22:06		10:00		22:03
12		10:06		22:12		10:12		22:18		10:06		22:06		10:09		22:06		10:06		22:06		10:09	16:06	22:09		10:06		22:06
13		10:06		22:12		10:12		22:18		10:06		22:06		10:09		22:06		10:06		22:06		10:09		22:09		10:06	16:00	22:06
14		10:12		22:15		10:18		22:21		10:12		22:09		10:15		22:09		10:12		22:09		10:15		22:12		10:12	16:06	22:09
15		10:12		22:15		10:18		22:21		10:12		22:09		10:15		22:09		10:12		22:09		10:15		22:12		10:12		22:09
16		10:18		22:21		10:24		22:27		10:18		22:15		10:21		22:15		10:18		22:15		10:21		22:18		10:18		22:15
17		10:18		22:21		10:24		22:27		10:18		22:15		10:21		22:15		10:18		22:15		10:21		22:18		10:18		22:15
18		10:24		22:27		10:30		22:33		10:24		22:21		10:27		22:21		10:24		22:21		10:27		22:24		10:24		22:21
19		10:24				10:30				10:24				10:27				10:24				10:27				10:24
20		10:27				10:42				10:27				10:30				10:27				10:30				10:27
21		10:27				10:42		22:33								22:21		10:27
22		10:30				10:48		22:36								22:24		10:30
23		10:30				10:48				10:27				10:30				10:30				10:30				10:27
24		10:42				11:00				10:36				10:42				10:39				10:39				10:39
25	4:00	10:42	16:00		4:00	11:00	16:00		4:00	10:36	16:00		4:00	10:42	16:00		4:00	10:39	16:00		4:00	10:39	16:06		4:00	10:39	16:06
26	4:06	10:54	16:27		4:06	11:09	16:18		4:06	10:45	16:18		4:06	10:51	16:18		4:06	10:48	16:18		4:06	10:48	16:24		4:06	10:48	16:24
27			16:27			11:09	16:18				16:18				16:18				16:18				16:24				16:24
28			17:06			11:15	16:54				16:51				16:51				16:57				16:54				17:00
29	4:06		17:06	22:27	4:06		16:54		4:06		16:51		4:06		16:51		4:06		16:57	22:21	4:06		16:54		4:06		17:00
30	4:12		17:36	22:30	4:12		17:24		4:12		17:21		4:12		17:21		4:12		17:27	22:24	4:12		17:24		4:12		17:30
31		10:54	17:36		4:12	11:15	17:24			10:45	17:21			10:51	17:21			10:48	17:27			10:48	17:24			10:48	17:30
32		11:00	17:48		4:15	11:21	17:36			10:51	17:33			10:57	17:33			10:54	17:48			10:54	17:36			10:54	17:42
33	4:12	11:00			4:15	11:21			4:12	10:51			4:12	10:57			4:12	10:54			4:12	10:54			4:12	10:54
34	4:36	11:06			4:30	11:27			4:33	10:57			4:27	11:03			4:33	11:00			4:27	11:00			4:27	11:00
Sheet1

 

[gnaga]

When I use the given formula for the conditional formatting I get the below result. What could not get what I am doing is not correct.

Book1
	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	AC	AD	AE	AF	AG
4
5		Sun				Mon				Tue				Wed				Thu				Fri				Sat
6		4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00
7					22:00				22:00				22:00				22:00				22:00				22:00				22:00
8					22:09				22:12				22:03				22:03				22:03				22:06				22:03
9									22:12
10									22:15
11			10:00		22:09		10:00		22:15		10:00		22:03		10:00		22:03		10:00		22:03		10:00	16:00	22:06		10:00		22:03
12			10:06		22:12		10:12		22:18		10:06		22:06		10:09		22:06		10:06		22:06		10:09	16:06	22:09		10:06		22:06
13			10:06		22:12		10:12		22:18		10:06		22:06		10:09		22:06		10:06		22:06		10:09		22:09		10:06	16:00	22:06
14			10:12		22:15		10:18		22:21		10:12		22:09		10:15		22:09		10:12		22:09		10:15		22:12		10:12	16:06	22:09
15			10:12		22:15		10:18		22:21		10:12		22:09		10:15		22:09		10:12		22:09		10:15		22:12		10:12		22:09
16			10:18		22:21		10:24		22:27		10:18		22:15		10:21		22:15		10:18		22:15		10:21		22:18		10:18		22:15
17			10:18		22:21		10:24		22:27		10:18		22:15		10:21		22:15		10:18		22:15		10:21		22:18		10:18		22:15
18			10:24		22:27		10:30		22:33		10:24		22:21		10:27		22:21		10:24		22:21		10:27		22:24		10:24		22:21
19			10:24				10:30				10:24				10:27				10:24				10:27				10:24
20			10:27				10:42				10:27				10:30				10:27				10:30				10:27
21			10:27				10:42		22:33								22:21		10:27
22			10:30				10:48		22:36								22:24		10:30
23			10:30				10:48				10:27				10:30				10:30				10:30				10:27
24			10:42				11:00				10:36				10:42				10:39				10:39				10:39
25		4:00	10:42	16:00		4:00	11:00	16:00		4:00	10:36	16:00		4:00	10:42	16:00		4:00	10:39	16:00		4:00	10:39	16:06		4:00	10:39	16:06
26		4:06	10:54	16:27		4:06	11:09	16:18		4:06	10:45	16:18		4:06	10:51	16:18		4:06	10:48	16:18		4:06	10:48	16:24		4:06	10:48	16:24
27				16:27			11:09	16:18				16:18				16:18				16:18				16:24				16:24
28				17:06			11:15	16:54				16:51				16:51				16:57				16:54				17:00
29		4:06		17:06	22:27	4:06		16:54		4:06		16:51		4:06		16:51		4:06		16:57	22:21	4:06		16:54		4:06		17:00
30		4:12		17:36	22:30	4:12		17:24		4:12		17:21		4:12		17:21		4:12		17:27	22:24	4:12		17:24		4:12		17:30
31			10:54	17:36		4:12	11:15	17:24			10:45	17:21			10:51	17:21			10:48	17:27			10:48	17:24			10:48	17:30
32			11:00	17:48		4:15	11:21	17:36			10:51	17:33			10:57	17:33			10:54	17:48			10:54	17:36			10:54	17:42
33		4:12	11:00			4:15	11:21			4:12	10:51			4:12	10:57			4:12	10:54			4:12	10:54			4:12	10:54
34		4:36	11:06			4:30	11:27			4:33	10:57			4:27	11:03			4:33	11:00			4:27	11:00			4:27	11:00
35
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E7:AF34	Expression	=AGGREGATE(15,6,E7:E34/(E7:E34>E6+1/24),1)	text	NO

 

[Phuoc]

Use this formula for CF:

=E7-E$6>1/24

 

[Peter_SSs]

I would like to highlight the hour which is crossing one hour from the specific time (Row 5) as I given in my example.

It actually looks like you meant row 6 in your example?

To get the results shown in post #5 you need to assess each cell value against the result of the AGGREGATE formula and you also need to check that the cell is the first cell that matches the AGGREGATE formula.

So, try this, applied to the range E7:AF34

gnaga.xlsm
	E	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	AF
5	Sun				Mon				Tue				Wed				Thu				Fri				Sat
6	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00
7				22:00				22:00				22:00				22:00				22:00				22:00				22:00
8				22:09				22:12				22:03				22:03				22:03				22:06				22:03
9								22:12
10								22:15
11		10:00		22:09		10:00		22:15		10:00		22:03		10:00		22:03		10:00		22:03		10:00	16:00	22:06		10:00		22:03
12		10:06		22:12		10:12		22:18		10:06		22:06		10:09		22:06		10:06		22:06		10:09	16:06	22:09		10:06		22:06
13		10:06		22:12		10:12		22:18		10:06		22:06		10:09		22:06		10:06		22:06		10:09		22:09		10:06	16:00	22:06
14		10:12		22:15		10:18		22:21		10:12		22:09		10:15		22:09		10:12		22:09		10:15		22:12		10:12	16:06	22:09
15		10:12		22:15		10:18		22:21		10:12		22:09		10:15		22:09		10:12		22:09		10:15		22:12		10:12		22:09
16		10:18		22:21		10:24		22:27		10:18		22:15		10:21		22:15		10:18		22:15		10:21		22:18		10:18		22:15
17		10:18		22:21		10:24		22:27		10:18		22:15		10:21		22:15		10:18		22:15		10:21		22:18		10:18		22:15
18		10:24		22:27		10:30		22:33		10:24		22:21		10:27		22:21		10:24		22:21		10:27		22:24		10:24		22:21
19		10:24				10:30				10:24				10:27				10:24				10:27				10:24
20		10:27				10:42				10:27				10:30				10:27				10:30				10:27
21		10:27				10:42		22:33								22:21		10:27
22		10:30				10:48		22:36								22:24		10:30
23		10:30				10:48				10:27				10:30				10:30				10:30				10:27
24		10:42				11:00				10:36				10:42				10:39				10:39				10:39
25	4:00	10:42	16:00		4:00	11:00	16:00		4:00	10:36	16:00		4:00	10:42	16:00		4:00	10:39	16:00		4:00	10:39	16:06		4:00	10:39	16:06
26	4:06	10:54	16:27		4:06	11:09	16:18		4:06	10:45	16:18		4:06	10:51	16:18		4:06	10:48	16:18		4:06	10:48	16:24		4:06	10:48	16:24
27			16:27			11:09	16:18				16:18				16:18				16:18				16:24				16:24
28			17:06			11:15	16:54				16:51				16:51				16:57				16:54				17:00
29	4:06		17:06	22:27	4:06		16:54		4:06		16:51		4:06		16:51		4:06		16:57	22:21	4:06		16:54		4:06		17:00
30	4:12		17:36	22:30	4:12		17:24		4:12		17:21		4:12		17:21		4:12		17:27	22:24	4:12		17:24		4:12		17:30
31		10:54	17:36		4:12	11:15	17:24			10:45	17:21			10:51	17:21			10:48	17:27			10:48	17:24			10:48	17:30
32		11:00	17:48		4:15	11:21	17:36			10:51	17:33			10:57	17:33			10:54	17:48			10:54	17:36			10:54	17:42
33	4:12	11:00			4:15	11:21			4:12	10:51			4:12	10:57			4:12	10:54			4:12	10:54			4:12	10:54
34	4:36	11:06			4:30	11:27			4:33	10:57			4:27	11:03			4:33	11:00			4:27	11:00			4:27	11:00
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E7:AF34	Expression	=AND(E7=AGGREGATE(15,6,E$8:E$34/(E$8:E$34>E$6+1/24),1),E7<>E6)	text	NO

 

[gnaga]

It actually looks like you meant row 6 in your example?

To get the results shown in post #5 you need to assess each cell value against the result of the AGGREGATE formula and you also need to check that the cell is the first cell that matches the AGGREGATE formula.

So, try this, applied to the range E7:AF34

gnaga.xlsm
	E	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	AF
5	Sun				Mon				Tue				Wed				Thu				Fri				Sat
6	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00
7				22:00				22:00				22:00				22:00				22:00				22:00				22:00
8				22:09				22:12				22:03				22:03				22:03				22:06				22:03
9								22:12
10								22:15
11		10:00		22:09		10:00		22:15		10:00		22:03		10:00		22:03		10:00		22:03		10:00	16:00	22:06		10:00		22:03
12		10:06		22:12		10:12		22:18		10:06		22:06		10:09		22:06		10:06		22:06		10:09	16:06	22:09		10:06		22:06
13		10:06		22:12		10:12		22:18		10:06		22:06		10:09		22:06		10:06		22:06		10:09		22:09		10:06	16:00	22:06
14		10:12		22:15		10:18		22:21		10:12		22:09		10:15		22:09		10:12		22:09		10:15		22:12		10:12	16:06	22:09
15		10:12		22:15		10:18		22:21		10:12		22:09		10:15		22:09		10:12		22:09		10:15		22:12		10:12		22:09
16		10:18		22:21		10:24		22:27		10:18		22:15		10:21		22:15		10:18		22:15		10:21		22:18		10:18		22:15
17		10:18		22:21		10:24		22:27		10:18		22:15		10:21		22:15		10:18		22:15		10:21		22:18		10:18		22:15
18		10:24		22:27		10:30		22:33		10:24		22:21		10:27		22:21		10:24		22:21		10:27		22:24		10:24		22:21
19		10:24				10:30				10:24				10:27				10:24				10:27				10:24
20		10:27				10:42				10:27				10:30				10:27				10:30				10:27
21		10:27				10:42		22:33								22:21		10:27
22		10:30				10:48		22:36								22:24		10:30
23		10:30				10:48				10:27				10:30				10:30				10:30				10:27
24		10:42				11:00				10:36				10:42				10:39				10:39				10:39
25	4:00	10:42	16:00		4:00	11:00	16:00		4:00	10:36	16:00		4:00	10:42	16:00		4:00	10:39	16:00		4:00	10:39	16:06		4:00	10:39	16:06
26	4:06	10:54	16:27		4:06	11:09	16:18		4:06	10:45	16:18		4:06	10:51	16:18		4:06	10:48	16:18		4:06	10:48	16:24		4:06	10:48	16:24
27			16:27			11:09	16:18				16:18				16:18				16:18				16:24				16:24
28			17:06			11:15	16:54				16:51				16:51				16:57				16:54				17:00
29	4:06		17:06	22:27	4:06		16:54		4:06		16:51		4:06		16:51		4:06		16:57	22:21	4:06		16:54		4:06		17:00
30	4:12		17:36	22:30	4:12		17:24		4:12		17:21		4:12		17:21		4:12		17:27	22:24	4:12		17:24		4:12		17:30
31		10:54	17:36		4:12	11:15	17:24			10:45	17:21			10:51	17:21			10:48	17:27			10:48	17:24			10:48	17:30
32		11:00	17:48		4:15	11:21	17:36			10:51	17:33			10:57	17:33			10:54	17:48			10:54	17:36			10:54	17:42
33	4:12	11:00			4:15	11:21			4:12	10:51			4:12	10:57			4:12	10:54			4:12	10:54			4:12	10:54
34	4:36	11:06			4:30	11:27			4:33	10:57			4:27	11:03			4:33	11:00			4:27	11:00			4:27	11:00
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E7:AF34	Expression	=AND(E7=AGGREGATE(15,6,E$8:E$34/(E$8:E$34>E$6+1/24),1),E7<>E6)	text	NO

Thanks Peter_SSs ??. I will apply and check your solution and let you know.

 

[gnaga]

Hi, Peter_SSs

Little starge behavior when I change the data value. Column "N" having two identical values both get formatted. Moreover if you could explain your formula would be grateful.

Book1
	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	AC	AD	AE	AF	AG
3
4
5		Sun				Mon				Tue				Wed				Thu				Fri				Sat
6		4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00	4:00	10:00	16:00	22:00
7					22:00				22:00				22:00				22:00				22:00				22:00				22:00
8					22:15				22:20				22:05				22:05				22:05				22:10				22:05
9									22:20
10									22:25
11			10:00		22:15		10:00		22:25		10:00		22:05		10:00		22:05		10:00		22:05		10:00	16:00	22:10		10:00		22:05
12			10:10		22:20		10:20		22:30		10:10		22:10		10:15		22:10		10:10		22:10		10:15	16:10	22:15		10:10		22:10
13			10:10		22:20		10:20		22:30		10:10		22:10		10:15		22:10		10:10		22:10		10:15		22:15		10:10	16:00	22:10
14			10:20		22:25		10:30		22:35		10:20		22:15		10:25		22:15		10:20		22:15		10:25		22:20		10:20	16:10	22:15
15			10:20		22:25		10:30		22:35		10:20		22:15		10:25		22:15		10:20		22:15		10:25		22:20		10:20		22:15
16			10:30		22:35		10:40		22:45		10:30		22:25		10:35		22:25		10:30		22:25		10:35		22:30		10:30		22:25
17			10:30		22:35		10:40		22:45		10:30		22:25		10:35		22:25		10:30		22:25		10:35		22:30		10:30		22:25
18			10:40		22:45		10:50		22:55		10:40		22:35		10:45		22:35		10:40		22:35		10:45		22:40		10:40		22:35
19			10:40				10:50				10:40				10:45				10:40				10:45				10:40
20			10:45				11:10				10:45				10:50				10:45				10:50				10:45
21			10:45				11:10		22:55								22:35		10:45
22			10:50				11:20		23:00								22:40		10:50
23			10:50				11:20				10:45				10:50				10:50				10:50				10:45
24			11:10				11:40				11:00				11:10				11:05				11:05				11:05
25		4:00	11:10	16:00		4:00	11:40	16:00		4:00	11:00	16:00		4:00	11:10	16:00		4:00	11:05	16:00		4:00	11:05	16:10		4:00	11:05	16:10
26		4:10	11:30	16:45		4:10	11:55	16:30		4:10	11:15	16:30		4:10	11:25	16:30		4:10	11:20	16:30		4:10	11:20	16:40		4:10	11:20	16:40
27				16:45			11:55	16:30				16:30				16:30				16:30				16:40				16:40
28				17:50			12:05	17:30				17:25				17:25				17:35				17:30				17:40
29		4:10		17:50	22:45	4:10		17:30		4:10		17:25		4:10		17:25		4:10		17:35	22:35	4:10		17:30		4:10		17:40
30		4:20		18:40	22:50	4:20		18:20		4:20		18:15		4:20		18:15		4:20		18:25	22:40	4:20		18:20		4:20		18:30
31			11:30	18:40		4:20	12:05	18:20			11:15	18:15			11:25	18:15			11:20	18:25			11:20	18:20			11:20	18:30
32			11:40	19:00		4:25	12:15	18:40			11:25	18:35			11:35	18:35			11:30	19:00			11:30	18:40			11:30	18:50
33		4:20	11:40			4:25	12:15			4:20	11:25			4:20	11:35			4:20	11:30			4:20	11:30			4:20	11:30
34		5:00	11:50			4:50	12:25			4:55	11:35			4:45	11:45			4:55	11:40			4:45	11:40			4:45	11:40
35
36
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E7:AF34	Expression	=AND(E7=AGGREGATE(15,6,E$8:E$34/(E$8:E$34>E$6+1/24),1),E7<>E6)	text	NO