Nested IF statements returning 'FALSE' on time check

Occasional Visitor

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"))))

 

Screenshot 2022-08-30 173116.jpg

1 Reply

@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.