Combine two if formulas?

[celquest]

Hello all,

I am tracking expiration dates on Insurance Documents. I have two IF formulas that I need to combine.

The Expiration Dates of the Docs are manually entered in Col. G (starting with G3). I figured out how to set the formula so that the spreadsheet will enter the word "EXPIRED" (in Col. H) if the date is today or previous. I would like the same formula to enter the words "THIS MONTH" if the date falls after today, up to the end of the month. Of course, I need the information to automatically update itself based on the days/month we are currently in. If neither of these conditions are met, the cell should stay blank.

I figured out the formulas individually - just don't know how to combine them. Here is what I have:

Expired: IF($G3<=TODAY(), "EXPIRED", "")

Expiring this Month: IF(AND($G3>TODAY(),$G3<
EOMONTH(TODAY(),0)),"THIS MONTH","")

This is prob really simple but I can't figure it out! Any help would be much appreciated. Thanks.

 

[Gerald Higgins]

Hi, welcome to the board.

Try this

=IF(G3<TODAY(),"EXPIRED",IF(MONTH(G3)=MONTH(TODAY()),"THIS p month?,??))<>< TODAY(),"Expired",IF(MONTH(G3)=MONTH(TODAY()),"This month",""))

 

[celquest]

Many Thanks!

I just tried this but one problem - my years range from 2010 - 2012 so this is also flagging items that are due to expire 8/2012.

Any suggestions?

Thanks again!!

 

[celquest]

I just figured it out - I added YEAR (duh!) and it works like a charm!

IF(G3<
TODAY(),"Expired",IF(MONTH($G3)*YEAR($G3)=MONTH(TODAY())*YEAR(TODAY()),"This month",""))

Thanks again for your help!!!