Help with if and/or

[Liz_I3]

Hi I can't get this to work Column K is a formula =IF(OR(H10 ="",H10 = "NULL"),"",H10-F10) and formatted as 37:30:55 example row 10 column K result is 105:09:12
Column C is just text
I want to have the condition met if the hours in K are less than or equal to 104:00:00 AND C equals Red OR Yellow


=IF(AND(TEXT(K10,"hh:mm")<="104:00",OR(C10="Red",C10="Yellow")),"Met",Missed")

Thanks
L

 

[Fluff]

Try
=TEXT(K10,"[h]:mm")

 

[Joe4]

Maybe try this:
=IF(AND(K10<=(104/24),OR(C10="Red",C10="Yellow")),"Met",Missed")

 

[Liz_I3]

Thanks but I am still not always getting the correct result. I think it is the time part that is not working they Red ,Yellow I think is ok

column K is 105:07:42 and I entered =IF(AND(K6<=(106/24),OR(C6="Red",C6="Yellow")),"Met","Missed") it returned Missed it should have been Met

I also tried Column K is 16:42:51 =IF(AND(TEXT(K375,"[h]:mm")<="106:00:00",OR(C375="Red",C375="Yellow")),"Met","Missed") the result was Missed should have been Met

L

 

[Fluff]

Both work for me

+Fluff New.xlsm
	C	D	E	F	G	H	I	J	K	L
10	Red								105:09:12	Met
11	Red								105:09:12	Met
Shipment

Cell Formulas
Range	Formula
L10	L10	=IF(AND(K10<=(106/24),OR(C10="Red",C10="Yellow")),"Met","Missed")
L11	L11	=IF(AND(TEXT(K11,"[h]:mm")<="106:00",OR(C11="Red",C11="Yellow")),"Met","Missed")

Are you sure that your times, are real times & not text?

 

[Joe4]

Please give us an example that is not working, where you tell us the exact content of all the cells being called by the formulas (including the H and F columns that column K uses).

 

[Liz_I3]

C	F	H	K	K formated as 37:30:50
Red	14/07/2020 18:04​	15/07/2020 10:47​	0.6964307​	16:42:52​

F and H are formatted as dd/mm/yyyy h:mm I should get Met but it is returning Missed. For the examples above were K is 105:09:12 I got Met also but when I copied
the formula down then I got errors above is an example of one that was wrong.
Thanks for you help

L

 

[Joe4]

I recreated the example above, and it returns "Met" for me.
Are you sure that you do not have any extra spaces or invisible characters before or after the entry in column C?
A simple LEN function can confirm this, i.e.
if you have "Red" in cell C10, then this formula:
=LEN(C10)
should return 3. If it doesn't, you have other things in there.

 

[Liz_I3]

I don't think so but will check Thanks

 

[Liz_I3]

Thank you very much I think it is working. I will try a few more variations and post back

L