Aug 30 2022 09:44 AM
I am trying to write a formula to show the below:
General rule: If actual (C2) or forecast (B2) time is less than start time (A2) + 15mins then return status (D2) "on track", if its more than or equal to start time + 15mins then return status "late"
What I am trying to also include is a rule to check if actual time is 'blank' then use the rule against forecast time but if actual time is filled then over-ride the forecast time formula and check against actual time instead. I've written the below formula but is returning 'FALSE' when i have filled in actual time. Please help!
=IF(C2="",IF(B2<A2+TIME(0,15,0),"On track",IF(B2>=A2+TIME(0,15,0),"Late",IF(C2<A2+TIME(0,15,0),"On track","Late"))))
Aug 30 2022 10:13 AM - edited Aug 30 2022 10:18 AM
@ENZO22M That is correct. Your first condition has no FALSE statements:
=IF(C2="",
IF(B2<A2+TIME(0,15,0),
"On track",
IF(B2>=A2+TIME(0,15,0),
"Late",
IF(C2<A2+TIME(0,15,0),
"On track",
"Late"
)
)
)
[here is where the "FALSE" statements for C2="" would be]
)
TIP: use ALT-ENTER to create new lines to help show the formula better
TIP2: consider using IFS() statement to reduce the cascading IF() statements.
after re-reading your post I think this is what you wanted:
=IF(C2="",
IF(B2<A2+TIME(0,15,0),
"On track",
"Late"
),
IF(C2<A2+TIME(0,15,0),
"On track",
"Late"
)
)
BTW if you highlight the above text you CAN paste it into excel IF you either click inside the formula bar (at the top) or use F2 to edit the cell.