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! |
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 |
