Nested IF

[MikeFrancis1959]

I have a excel formula that looks at 2 columns that contains dates or could be null and depending on the combination then the formula will produce the results. The formula is 

IF(AND([1st GI]>01/01/23,[2nd GI]>01/01/23),"Delivered", IF(AND([1st GI]>01/01/23,[2nd GI]= ""),"In Transit","Planning TBD")).

I have tried various formulas and I cannot get it to work, can someone help me with this formula?

Thanks,

Mike

[FinnCharlton]

Try:

IF

[1st GI]>2023-01-01 AND [2nd GI]>2023-01-01

THEN

"Delivered"

ELSEIF

[1st GI]>2023-01-01 AND ISEMPTY([2nd GI])

THEN

"In Transit"

ELSE

"Planning TBD"

ENDIF

Make sure your date fields are the correct data type (Date / DateTime).

[MikeFrancis1959]

Thanks for the quick reply, I will try it!

Mike

[MikeFrancis1959]

I keep getting the error " Parse error at char 85, invalid type in operator >. Expression 1. This is where the " ANS ISEMPTY" starts, I am not sure what I need to do

[BS_THE_ANALYST · Accepted solution]

@MikeFrancis1959 Looks like @FinnCharlton's logic is correct. Just make sure you put quotes around your dates like below:

IF

[1st GI]>"2023-01-01" AND [2nd GI]>"2023-01-01"

THEN

"Delivered"

ELSEIF

[1st GI]>"2023-01-01" AND ISEMPTY([2nd GI])

THEN

"In Transit"

ELSE

"Planning TBD"

ENDIF

[BS_THE_ANALYST]

@MikeFrancis1959  i.e: no quotes

vs quotes:

[MikeFrancis1959]

That worked, I am still new at this and I have to remember to put the quotes in there to work. I keep all my formulas in a file for future use and I appreciate all the help from this forum!

Mike