Logical test? Showing when a date has expired

Hi all, I’m sure this will be an easy one for you lot, but its beyond me!

Basically I have two columns, one with a list of Inception dates and one with a list of Expiry dates, and next to that I want to create a column that tells me if it is current or expired. I was thinking the best way to do this would be to have one cell with “Expired” written in it and one with “Current” written in it and use them for references in a logical test?

Obviously I’m going to need some sort of reference to the current date (the date you open the spreadsheet) ideally I guess it would be in the form of a web query so I can hit the refresh button and the date simply updates? Is this the best way or is there something simpler?

The only other thing that would be good is to get the 3rd column showing “Expired” or “Current” to automatically apply a green cell background to the “Current” cells and a red background to the “Expired” cells.

Thanks in advance for you help and time its much appreciated.


WTB

Hi,

with the expiry date in A1, use

=if(today()>A1,"Expired","Current")

Then use conditional formatting to color the cell green if it has the text "Current" and red if it has "Expired".