Creating a nested IF statement for mulitple expiration dates

[tstage]

Greetings -

I am attempting to create a working inventory that will automatically detect expiration dates in three separate columns (which I'll call Column A, Column B, and Column C) and place the outcome ("Expired") in Column D. What I want the formula to do is to look at Column C first* to see if there is a date present. If there isn't, I then want it to look at Column B. If Column B is blank then I want it to look at Column A. If Column A, B, and C are all blank I want it to return a blank cell in Column D.

Anytime an expiration date is exceeded, the word "Expired" will automatically become visible in Column D. This will alert the user that the product is expired and should be moved to a quarantined area, which will be automatically noted in another column (E) using the following simple IF statement:

=IF([Column D]="Expired","Quarantine","")

I can create a formula to work for one column but three columns is alluding me. Here's the IF Statement I came up with for one column (A), which works for any date prior to "Today":

=IF(OR(ISBLANK([Column A]),[Column A]<=TODAY()),"","Expired")

What I'm guessing I'm looking for is a nested IF statement that will cover the following six scenarios and properly display the outcome in Column D:

• Column A expiration date is not expired - Column D remains blank
• Column A expiration date is expired - Column D displays "Expired"
• Column A expiration date is expired but Column B expiration date is not expired - Column D remains blank
• Column A & Column B expiration date is expired - Column D displays "Expired"
• Column A & Column B expiration date is expired but Column C expiration date is not expired - Column D remains blank
• Column A, Column B, and Column C expiration date is expired - Column D displays "Expired"

So, if Column B has an expiration date, Column D would need to ignore the expired date in Column A and only respond to the unexpired or expired date in Column B. The same would be true for Column A & B when Column C is occupied.

I hope that all makes sense. I appreciate any suggestions or tips any of you might be able to give me.

Thanks,

Tom

 

[Special-K99]

Does this work?

=IF(C1>TODAY(),"Expired",IF(B1>TODAY(),"Expired",IF(A1>TODAY(),"Expired","")))

 

[tstage]

Hi Special-K99,

Thanks for the reply. Unfortunately, the formula doesn't work. I plugged it into Column D. Even if I place a future date in the same row of either Column B or Column C, "Expired" remains in Column D. What I want to achieve is for Column D to reflect the status of the most recent date - i.e. albeit Column A, B, or C - and whether it is expired or unexpired.

Tom

 

[Special-K99]

Try this

=IF(A1&B1&C1="","",IF(AND(C1 < TODAY(),C1 <> ""),"Expired",IF(AND(B1 < TODAY(),B1 <> ""),"Expired",IF(AND(A1 < TODAY(),A1 <> ""),"Expired",""))))

or this

=IF(A1&B1&C1="","",IF(MAX(A1,B1,C1) < TODAY(),"Expired",""))

 

[tstage]

Special-K99,

Thanks. While I didn't use your 2nd formula, your 1st formula actually helped me discover an IF(OR statement that seems to work:

=IF(OR(C1>=TODAY(),B1>=TODAY(),A1>=TODAY()),"","Expired")

When I substitute expired and unexpired dates in any of the columns, "Expired" displays or disappears as it should - Woohoo!

Thanks for the "seed" to help me discover the correct formula. =O)

Tom