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!

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" <(E-Mail Removed)> wrote in message
news:A01C227B-B359-43F3-B39E-(E-Mail Removed)...
> 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!
>
>
>

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

"Conan Kelly" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:A01C227B-B359-43F3-B39E-(E-Mail Removed)...
> > 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!
> >
> >
> >
>
>
>

Glad to help,

Conan




"WSI" <(E-Mail Removed)> wrote in message
news:3E0B7E6F-E0DF-4A9E-8A1E-(E-Mail Removed)...
> That worked great - the =today() is what I needed.
> Thanks Conan
> -Shane
>
> "Conan Kelly" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:A01C227B-B359-43F3-B39E-(E-Mail Removed)...
>> > 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,
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.
--
Paper is power...


"Conan Kelly" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:A01C227B-B359-43F3-B39E-(E-Mail Removed)...
> > 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!
> >
> >
> >
>
>
>

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.



On Dec 20, 11:03 am, J.D. <J...@discussions.microsoft.com> wrote:
> 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.
> --
> Paper is power...
>
> "Conan Kelly" wrote:
> > 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" <W...@discussions.microsoft.com> wrote in message
> >news:A01C227B-B359-43F3-B39E-(E-Mail Removed)...
> > > 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!