Conditional Formula based on current date

[WSI]

Hello -
I have a list of expiration dates and I want to highlight the expired ones.
The list is tranposed from another sheet so it is in a column instead of a
row, so I want to be able to do this without sorting.
For example, here is what the data looks like in
sheet1:
12/31/2007 10/31/2007


sheet2:
12/31/2007
10/31/2007

With the formula being {=TRANSPOSE(Sheet1!A1:B1)} to get these values into
columns (There 86 rows in my actual sheet)

How can I build a conditional format to get the cells to highlight red if
they are earlier than todays date, or yellow if they are within 30 days?

Help is much appreciated!

 

[Conan Kelly]

WSI,

1. Select all of the cells that have the dates you want to highlight.
2. Format > Conditional Formatting
3. Cell Value is
4. less than
5. =Today()
6. Click the "Format..." button
7. Choose the color red from the "Patterns" tab
8. Click "OK" on the "Format Cells" dialog to get back to the
"Conditional Formatting" dialog
9. Click the "Add>>" button
10. Cell Value Is
11. Between
12. =Today()
13. =Today()+30
14. Click the 2nd "Format..." button
15. Choose the color yellow from the "Patterns" tab
16. Click "OK" on the "Format Cells" dialog to get back to the "Conditional
Formatting" dialog
17. Clisk "OK" on the "Conditinal Formatting" dialog.

HTH,

Conan

 

[WSI]

That worked great - the =today() is what I needed.
Thanks Conan
-Shane

 

[Conan Kelly]

Glad to help,

Conan

That worked great - the =today() is what I needed.
Thanks Conan
-Shane

 

[J.D.]

Conan,
Your response was perfect by the way... but I want to make the formula
display "EXPIRED" in another cell when the item has expired. So I want it
to show red shading in the background and then display "EXPIRED" in a
neighboring cell. Does that make sense? Thanks a lot.

 

[iliace]

In your cell where you want to say EXPIRED, use this worksheet
formula:

=IF(A1<TODAY(),"EXPIRED","")

Assumes A1 is the cell next to it, in other words you're in B1. You
can use this in combination with conditional formatting, but the
formatting itself will only affect font (except size), number format,
borders, and fill - not what's displayed in the cell.

You could also technically set a custom number format on conditional
formatting. In the Number tab, select Custom, and use this formatting
code:

"EXPIRED"

Any value in the cell will display "EXPIRED" if you do this, so you
can use this as a custom format for the condition, in your date cell,
instead of highlighting it.