Check value of cells against condition?

[jgruberman]

Hello,

I am looking to check a table of sports team names, times and ranking and if their ranking is 3 or lower and their game time is 9pm or later, to flag them somehow, perhaps with another cell with a yes/no or just any sort of identifier that the team fits the specified criteria. Here is an excerpt:

2/4	9:45 PM	Warriors D1	Bulldogs D1	D1	Valley Ranch StarCenter	Green	1	5
2/4	3:30 PM	GRITR D2	Fighting Squids D2	D2	Valley Ranch StarCenter	Hitchcock	5	4

 

[etaf]

i wonder if that is copying in the cell as a text value and not as a number
in which case try
=IF( AND( B2 >=timevalue("21:00:00"), H2*1<=3) , "Yes", "No")
OR
=IF( AND( B2 >=timevalue("21:00:00"), I2*1<=3) , "Yes", "No")

again just checked in goolge sheets as a TEXT value , which would be left justified and NOT right justified , as a number
I notice when copied here , regardless its left justified - so cannot tell on the forum, you may see in google sheets itself

so *1
or even +0
will change a text number to a real number

 

[jgruberman]

=IF( AND( B2 >=timevalue("21:00:00"), I2*1<=3) , "Yes", "No") --- did not seem to work. I removed the formula from the field and just put 3 in the cell and it still did not show Yes. Perhaps it's the timevalue not working correctly

 

[etaf]

yep sounds like it maybe
try typing in 22:00
and see what happens
Maybe thats TEXT and not a real time
cant see how to format the cell on google sheets to show just general / number

EDIT OK, found it , on the menu bar
try foramtting as a number
should get 0.92 for 22:00
actually in formula bar
0.916666666666667

 

[Joe4]

I apologize, I assumed that if I wasn't posting in the Excel specific forum that it implied I was using Sheets. That was my mistake.

No, the forum is really a catch-all for ALL non-Excel type requests, not just for Google Sheets. So people will also post questions about Open Office, Outlook, etc (virtually any other program).
So we ask that you just mention somewhere in your thread title and/or original post that this question pertains to "Google Sheets".

Thanks

 

[jgruberman]

yep sounds like it maybe
try typing in 22:00
and see what happens
Maybe thats TEXT and not a real time
cant see how to format the cell on google sheets to show just general / number

EDIT OK, found it , on the menu bar
try foramtting as a number
should get 0.92 for 22:00
actually in formula bar
0.916666666666667

I made it even simpler for troubleshooting and changed the time to a number of "10" and made the formula this:
=IF( AND( D2 >=10, K2<=3) , "Yes", "No")

so even though D2 is 10 and K2 is 3, it is still returning "No"

 

[jgruberman]

I was able to figure this out. The original formula was correct that you posted, I was editing the cell values for the wrong cell. This is the working formula(which is your original reply I believ):
=IF(AND(D19>=TIMEVALUE("21:00"),K19<=3),"Yes","No")

Thank you for your assistance

 

[etaf]

you are welcome