Forum Discussion

ENZO22M's avatar
ENZO22M
Copper Contributor
Aug 30, 2022

Nested IF statements returning 'FALSE' on time check

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 e...
Formulas and Functions
mtarler's avatar
mtarler
Silver Contributor
Aug 30, 2022

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.

 

 

Resources