If Formula with multiple criteria to have return value depending on dates

[Luna_Maria]

Hi All,

I am already out of option trying to get the right formula for my scenario.
I have 3 columns 1. Panned Date(Column A), 2. Completion Date(Column B), 3. Status(Column C), what I am trying to achieve is depending on the 1st 2 columns I'll be having different status.

Here's what I am trying to test:

=If(And(B1="",A1>=Today()),"On Track","") - I am returning correct value in here, but since i have different scenario I need to add more ifs.

here where the problem arise, when i add more, and i needed to have 2 more ifs.
=If(And(B1="",A1>=Today(),"On Track","",if(and(isnumber(B1),"Completed",""))

what is the right thing to do?

 

[Habtest]

Please see if it works:

=IFS(AND(B1="",A1>=TODAY()),"On Track",AND(ISNUMBER(B1),A1>=TODAY()),"Completed")

 

[Luna_Maria]

Please see if it works:

=IFS(AND(B1="",A1>=TODAY()),"On Track",AND(ISNUMBER(B1),A1>=TODAY()),"Completed")

Thank you, that's it. It solved my problem, I appreciate it

 

[Habtest]

Glad to help, thanks for the feedback.