Search code examples
excelexcel-formulaexcel-2010

How to formula if with multi conditions in Excel

I am trying formula if with multi conditions in Excel.

but the result #VALUE!is there is something wrong with my formula please guide me

Thanks

DATE IN/OUT TIME DEF IN DEF OUT RESULT
13-03-24 IN 7:35:41 8:00:00 15:30:00
13-03-24 OUT 15:42:10 8:00:00 15:30:00
13-03-24 IN 8:35:41 8:00:00 15:30:00
13-03-24 OUT 15:42:10 8:00:00 15:30:00
13-03-24 OUT 13:00:10 8:00:00 15:30:00
IN 
=IF(A2="",""),IF(AND(B2="OUT",E2<=C2),"NORMAL","LATE"),IF(AND(B2="IN",D2<=C2),"NORMAL","LATE")

Result from above formula

DATE IN/OUT TIME DEF IN DEF OUT RESULT
13-03-24 IN 7:35:41 8:00:00 15:30:00 #VALUE!
13-03-24 OUT 15:42:10 8:00:00 15:30:00 #VALUE!
13-03-24 IN 8:35:41 8:00:00 15:30:00 #VALUE!
13-03-24 OUT 15:42:10 8:00:00 15:30:00 #VALUE!
13-03-24 OUT 13:00:10 8:00:00 15:30:00 #VALUE!
IN #VALUE!

enter image description here

Desired Result

DATE IN/OUT TIME DEF IN DEF OUT RESULT
13-03-24 IN 7:35:41 8:00:00 15:30:00 NORMAL
13-03-24 OUT 15:42:10 8:00:00 15:30:00 NORMAL
13-03-24 IN 8:35:41 8:00:00 15:30:00 LATE
13-03-24 OUT 15:42:10 8:00:00 15:30:00 NORMAL
13-03-24 OUT 13:00:10 8:00:00 15:30:00 LATE
IN
Solution

  • This works ...

    =IF(A2="","",IF(AND(B2="IN",C2<=D2),"NORMAL",IF(AND(B2="OUT",E2<=C2),"NORMAL","LATE")))

    ... or this ...

    =IF(A2="","",IF(OR(AND(B2="IN",C2<=D2),AND(B2="OUT",E2<=C2)),"NORMAL","LATE"))