Microsoft Excel 2016 suites

I'm trying to write a formula that includes multiple conditions to return the correct value. In the example below (I've recreated it as best I could) I am tracking tenant's insurance documents. The formula will go in column B. I need it to return "Current" if the date in column C is tomorrow or later, "Expired" if column C is today or earlier, and "Missing" if column C is blank AND column A does not say "VACANT." If column A is VACANT, B and C both need to be blank. So far I've come up with

=IF(C2<=TODAY(),"Expired", IF(C2>TODAY(),"Current", IF(C2="", "Missing")))

Unfortunately because of <=TODAY, I guess it reads that to mean blank as well, so the last condition for Missing is ignored. Every blank cell returns "Expired." And I can't figure out how to have it look at Column A to see if it says VACANT.

Is there a formula that can make this work?

A B C 1 Name Status Expiration Date 2 VACANT 3 Tenant 1 Current 03/18/2019 4 Tenant 2 Expired 11/21/2017 5 VACANT 6 Tenant 3 Current 10/01/2019 7 Tenant 4 Missing 8 Tenant 5 Current 06/01/2019

message edited by tgordon21

✔ Best Answer

This should work. =IF(A2="VACANT","", IF(C2>TODAY(),"Current",IF(AND(C2<>"",C2<=TODAY()),"Expired","Missing")))

The key thing remember about the IF function is that it stops as soon as it finds a TRUE clause. Therefore, if A2 = "VACANT", return a blank cell and stop. If not, then you can move on and do your comparisons. If none of the comparisons are TRUE, then return "Missing".

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Hang about for pholks like DerbyDad03 and Mike McConaghy and hidee663... they will take you through it all... And there are a few others here too of similar guru status...

First, please click on the How-To link at the end of this post and read the instructions on how to post example data in this forum. Then edit your post so that the data is formatted so that we can read it easier. Please make sure that you use Column letters and Row numbers. BTW...

This clause is checking for a space, not a blank cell:

IF(C1=" ", "Missing")

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Thanks for the tip. I wasn't sure how to make it look like an actual table. Regarding IF(C1=" ", "Missing"), thanks for catching that. I was playing around and put the space in there to see if it made a difference, but it still came back as Expired everywhere that was blank. I just forgot to take that space out before I copied it here.

Update: I've tried this:

=IF(C2<=TODAY(),"Expired", IF(C2>TODAY(),"Current", IF(AND(C2="",A2<>"VACANT"), "Missing")))

But I'm still getting "Expired" in every cell that is blank. Not sure what to try next.

This should work. =IF(A2="VACANT","", IF(C2>TODAY(),"Current",IF(AND(C2<>"",C2<=TODAY()),"Expired","Missing")))

The key thing remember about the IF function is that it stops as soon as it finds a TRUE clause. Therefore, if A2 = "VACANT", return a blank cell and stop. If not, then you can move on and do your comparisons. If none of the comparisons are TRUE, then return "Missing".

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

This worked perfect! Thanks for your help!

Ask Your Question

Weekly Poll

Do you think Deepfakes are a major problem?

Discuss in The Lounge

Poll History