How To Find "Greater Than" A Specific Time ?

[Aitch]

I have 2 columns showing meeting times with people - one with the name and the other with a 24 hour time - how do I identify which people have any meetings after 6pm = Bob, Brian, Brenda, Joe

Tom	09:00
****	10:30
Brenda	11:00
John	13:00
Joe	17:00
James	17:30
Bob	19:00	Yes
Brian	20:00	Yes
Brenda	20:30	Yes
Joe	21:00	Yes

I'm sure an INDEX/MATCH or COUNTIF>1 could do this but I can't figure out how to use a time value greater than 18:00!

Thanks for the help!

 

[AhoyNC]

In Excel 24 hrs = 1 day, so 6 pm (18:00) would be 18/24 or 3/4.

Try:

Book1
	A	B	C	D
1	Name	Time		After 6 pm
2	Tom	9:00		Bob
3	****	10:30		Brian
4	Brenda	11:00		Brenda
5	John	13:00		Joe
6	Joe	17:00
7	James	17:30
8	Bob	19:00	Yes
9	Brian	20:00	Yes
10	Brenda	20:30	Yes
11	Joe	21:00	Yes
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=IFERROR(INDEX($A$2:$A$11,AGGREGATE(15,6,(ROW($A$2:$A$11)-ROW($A$2)+1)/($B$2:$B$11>=18/24),ROWS($D$2:D2))),"")

 

[MARK858]

Or you might find it more intuitive to use TIMEVALUE("18:00"). It is exactly the same formula as AhoyNC has posted but just with the extra function call.

Book1
	A	B	C	D
1	Name	Time		After 6 pm
2	Tom	09:00		Bob
3	****	10:30		Brian
4	Brenda	11:00		Brenda
5	John	13:00		Joe
6	Joe	17:00
7	James	17:30
8	Bob	19:00	Yes
9	Brian	20:00	Yes
10	Brenda	20:30	Yes
11	Joe	21:00	Yes
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=IFERROR(INDEX($A$2:$A$11,AGGREGATE(15,6,(ROW($A$2:$A$11)-ROW($A$2)+1)/($B$2:$B$11>=TIMEVALUE("18:00")),ROWS($D$2:D2))),"")

 

[mumps]

Another approach:
Enter 18:00 in cell D1
Enter this formula in C2 and copy it down: =IF(INT(B2*24)>INT($D$1*24)+1,"Yes","")

 

[Aitch]

Thank you so much! These all work... but are way beyond my understanding lol!

I've tried again using TIMEVALUE at more my level like this - but is there a clever way to use COUNTIFS where it calculates TIMEVALUE internally without actually creating the extra column?

NAME	TIME	"TIMEVALUE"	RESULT	FORMULA
Dave	09:00	0.375	NO	=IF (COUNTIFS (A2:A , A2 , C2:C , ">0.75")>0,"YES","NO")
Brenda	10:30	0.438	YES
John	11:00	0.459	NO
Joe	13:00	0.541	YES
James	17:00	0.708	NO
Joe	17:30	0.729	YES
Brian	19:00	0.791	NO
Brenda	20:00	0.833	YES
Joe	21:00	0.875	YES

 

[Dave Patton]

I am not clear on what your are asking or require.
1. You can calculate the result a variety of ways; review C2:E2
2. You can do a count see F2 or G2.
If you want a list without a formula, use a Data Table and filter by Result.

T202010b.xlsm
	A	B	C	D	E	F	G
1	Name	Time	18:00			Count	Count
2	Bob	19:00	TRUE	TRUE	TRUE	2	2
3	Susan	14:10	FALSE	FALSE	FALSE
4	Bill	20:10	TRUE	TRUE	TRUE
5	Jack	09:10	FALSE	FALSE	FALSE
6c

Cell Formulas
Range	Formula
C1	C1	=TIME(18,0,0)
C2:C5	C2	=B2>$C$1
D2:D5	D2	=B2>TIME(18,0,0)
E2:E5	E2	=B2>0.75
F2	F2	=COUNTIF(B2:B32,">"&TIME(18,0,0))
G2	G2	=COUNTIF(B2:B32,">"&C1)

 

[Fluff]

You can also use your formula, but look at col B

=IF(COUNTIFS(A:A,A2,B:B, ">0.75")>0,"YES","NO")