Conditional Formatting A Date

[Peter W Soady \(UK\)]

Hi

I have a very large worksheet (65 Columns x 5000 plus Rows) where one column
is
formatted in the UK Date format (dd-mm-yy) and is a manual entry.

I am required to turn the date red when it is 28 days or less from today's
date (if you were looking at being able to do it this way
=IF(TODAY()+28<=cell date,Red,black))

However, I am not allowed to add any more columns.

Is there a way of doing this without having to do each row individually with
either ...

Conditional Formatting
Formulae
Macro
Visual Basic

Many thanks in advance

 

[Dave O]

Hi, Peter-
I got results with conditional formatting using this formula:
=AND(A1-TODAY()<=28,TODAY()<A1)

It turns the cell red if the cell date is in the future and 28 or
less days from now. The dd/mm/yy format is not relevant if the date is
a Microsoft serial date.

 

[Gord Dibben]

No extra columns needed.

Select the cells and Format>CF

Formula is: =A1<=TODAY()+28


Gord Dibben MS Excel MVP

 

[Peter W Soady \(UK\)]

Many thanks guys.

Your help is appreciated.

Peter

 

[tristan.smit]

I have a similar project I am working on but have one more criteria
that has to be evaluated before the conditional formatting is
applied. In my scenario I have expiry dates for products. I have
three ranges of dates that I have created conditional formatting for:

=AND(F29-TODAY()>=0,F29-TODAY()<=30)
=AND(F29-TODAY()>=0,F29-TODAY()<=60)
=AND(F29-TODAY()>=0,F29-TODAY()<=90)

Only problem is that I have one product that is an exception. Lets
call this product "Chews" which is listed in a separate column from
the expiry date. Is there a way for me to have the 3rd condition? In
english I would like the statement to be the following:

If Expiry Date is less than 60 days and Product = Chews OR Expiry Date
is less than 30 days

Any help would be greatly appreciated. Thanks.