Triple nesting if functions, dates - expiration

Hi all

Thank you for accepting me to this awesome forum. I have a question about nesting excel if and formulas. I need help with a formula that would allow me to show "Expiration", "Pending Expiration"and "OK". I would have an issue date (B2) 5/20/2012 expiration date (B3)5/20/2013. i tried this but its broken =if(b3<=TODAY(),"EXPIRED",IF(B3>=TODAY()+7,"PENDING EXPIRATION", IF(B3<=TODAY()+7,"OK"

1. Formula to show that the issuance has "EXPIRED"
2. Formula to show "PENDING EXPIRATION" if the date falls within one week of expiration
3. Formula to show that if the date is farther than a week away from expiration to show "OK"

Any help would be great,

Tom King

Hello,

May it's because of this? You mention that "... ... issuance has "EXPIRED" But on the very first IF statement, you're actually looking at the expiration date, not the issuance date?

b3<=TODAY(),"EXPIRED"

So instead of b3...isn't it suppose to be b2?

Hello,

May it's because of this? You mention that "... ... issuance has "EXPIRED" But on the very first IF statement, you're actually looking at the expiration date, not the issuance date?

b3<=TODAY(),"EXPIRED"

So instead of b3...isn't it suppose to be b2?

=IF(B2>=TODAY(),"EXPIRED",IF(AND(C2>0,C2<=7),"PENDING EXPIRATION",IF(C2<8,"OK"))), i just tried this formula by adding an additional column that subtracts the issue date from todays date but nothing. Going deeper down the rabbit hole.....

would you be able to upload a sample of your worksheet. It's just difficult visualize what you're trying to do.

i.e. on your first post, it's all in column B, on the latest post, you have column C.

Would be better if you could give a little more explanation, such as the difference between issue and expiration date, and how the determined result were derived

Date Expiration Sheet.xlsx







I hope this is able to give a visual

Thanks for the upload. I get what you're trying to do.

On any cell (say M2), copy and paste the formula below.

Please Login or Register  to view this content.

Originally Posted by dluhut

Thanks for the upload. I get what you're trying to do.

On any cell (say M2), copy and paste the formula below.

Please Login or Register  to view this content.

I think that's it! Awesome! thank you so much for all of your help.

Glad to be of help! Remember to mark this thread as 'Solved'