Meeting a Conditional Requirement in a Spreadsheet

Hi...

I have a spreadsheet that requires status, start dates and expiration dates. My goal is to create a function that will change the text in the "Status" column to "Expired" automatically when the "Expire" column has a date in the past. "Expired" is not the only text that is shown in the cells in the Status column. The additional entries may be New, Renewal, etc. Those need to stay the same.

Column O = PB Status = Text Only (ie., Expired, New, Renewal)
Column P = PB Start = Dates Only
Column Q = PB Expire = Dates Only

It was my thought initially to create a macro to perform this task, but I need the formula first and can't make mine work within the large range of cells I have. My attempt at the formula is as follows:

=IF(Q2:Q1000 < Today(), "Expired")

This doesn't work of course. I've also tried naming the ranges in the columns and placing them in the formula, but I can't figure it out.

Any help would be greatly appreciated. Thanks!

I think you just needed to complete your If fomula, try this instead


=IF(Q1<(TODAY()),"Expired","")

Then just drag the formula down the column, rather than doing the range, which I don't think works with dates, or at least never has for me.

Hi,

Thank you for your response! This formula does change the cells to expired when meeting the date criteria. The issue I have is that other entries (i.e., New, Renewal) are entered in the status column and the formula affects those when copying and changes to blank. I need those to stay the same until they expire. Any ideas?

Sorry you're going to have to give some more detail, what criteria are you using to determine if it's categorised as New or Renewal? Or are you wanting to manually enter it?

Hi djauncey,

New and Renewal is entered manually in the Status column. The formula you provided changes those cells to blank since they don't meet the date criteria. The New and Renewal dates vary. Not sure how to define this in the formula.

Example:

Status Start Expire
New 1/7/12 1/7/13
Renewal 12/1/11 12/1/13
Expired 12/1/11 12/1/12

Hi jross,

Hmm that makes it a bit more of a challenge, if you don't have specific criteria for the other options it means you won't really be able to write a formula for it.

I mean you could set up something like this:

New - start date> today

Current - start date <= today and expire date >= today

Expired - expire date < today

However otherwise the only suggestion would be to put another column in where you can manually write New / Renewal and then in the formula just change the "" at the end to the cell.