IF function giving error... do i need to add AND function as well?!

[bobletcs123]

Good Morning All,

Having issues with my IF function formula, and i'm sure its user error. For the life of me, can't figure out what issue is and how to resolve. Any help would be greatly appreciated. On my sheet, i'm trying to compare orig dates (Column A) vs actual dates (Column B); easy enough. But, some Column B fields have NA or Complete, which is where i'm running into trouble. My formula is as follows:

=IF(B2-A2>0,"Delay",(IF(B2=A2,"On-Time",(IF(B2-A2<0,"Early",(IF(B2="NA","Open",(IF(B2="Complete","Complete")))))))))

The first part works great, but can't seem to figure out when Column B has NA or Complete. Need NA to generate "Open" and Complete to generate "Complete"...

Thank you in advance!!

 

[Fluff]

You need to do the text values first, otherwise you get an error trying to subtract a value from text.
=IF(B2="NA","Open",IF(B2="Complete","Complete",IF(B2-A2>0,"Delay",IF(B2=A2,"On-Time",IF(B2-A2<0,"Early")))))

 

[Joe4]

Reverse the order of your IF statements, as if B2 has "Complete" or "NA" in it, you won't be able to do the math, and that can cause errors.
So check for that first, i.e.

=IF(B2="Complete","Complete",IF(B2="NA","Open",IF(B2-A2>0,"Delay",IF(B2-A2<0,"Early","On-Time"))))

EDIT: I am not quite as quick as Fluff this morning!

 

[Fluff]

You could also use
=IF(B2="NA","Open",IF(B2="Complete","Complete",CHOOSE(SIGN(B2-A2)+2,"Early","On-Time","Delay")))
which saves calculating B2 & A2 three times.

 

[bobletcs123]

Aghh! Stupid mistake :/ Thank you both so much!!.... coffee hasn't kicked in yet

 

[Fluff]

Glad we could help & thanks for the feedback