Using Conditional Formatting for Dates
 

I would like a date to turn red when older then 2 years? I don't know much
about Excel. Thank you,

Using Conditional Formatting for Dates
 

MOP,

Select your cells. Note the active (white) cell of your selection.
FOrmat - Conditional Formatting. Change "Cell value is" to "Formula is."
Paste this in (from here):

=A2<DATE(YEAR(TODAY())-2,MONTH(TODAY()), DAY(TODAY()))

Change A2 to the active cell of your selection. Click "Format" and choose
the formatting you want for the older years. OK your way out of it. This
will give you the formatting

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"MOP" wrote in message
...
I would like a date to turn red when older then 2 years? I don't know much
about Excel. Thank you,

Using Conditional Formatting for Dates
 

This worked perfectly. Thank You. Is there a place (or document) that will
give me common formulas?

TIA-MOP

"Earl Kiosterud" wrote:

MOP,

Select your cells. Note the active (white) cell of your selection.
FOrmat - Conditional Formatting. Change "Cell value is" to "Formula is."
Paste this in (from here):

=A2<DATE(YEAR(TODAY())-2,MONTH(TODAY()), DAY(TODAY()))

Change A2 to the active cell of your selection. Click "Format" and choose
the formatting you want for the older years. OK your way out of it. This
will give you the formatting

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"MOP" wrote in message
...
I would like a date to turn red when older then 2 years? I don't know much
about Excel. Thank you,

Using Conditional Formatting for Dates
 

"Earl Kiosterud" wrote:

Select your cells. Note the active (white) cell of your selection.
FOrmat - Conditional Formatting. Change "Cell value is" to "Formula is."
Paste this in (from here):

=A2<DATE(YEAR(TODAY())-2,MONTH(TODAY()), DAY(TODAY()))

Change A2 to the active cell of your selection. Click "Format" and choose
the formatting you want for the older years. OK your way out of it. This
will give you the formatting

I just successfully used an amended version of Earl's formula to
conditionally colour the text in cells with either today's date or a future
date :

=I9<DATE(YEAR(TODAY()),MONTH(TODAY()), DAY(TODAY()))1

and another variant to conditionally colour cells containing a past date :

=I9<DATE(YEAR(TODAY()),MONTH(TODAY()), DAY(TODAY()))-0

This allowed me to show overdue (ie past) dates in RED text

Thank you very much Earl - your help was much appreciated !!

Andrew Millman

Using Conditional Formatting for Dates
 

I don't know of a place with stock formulas. We roll our own.
DATE(YEAR(TODAY())-2,MONTH(TODAY()), DAY(TODAY())) returns a date two years
ago. By putting A2< ThatDate, we get TRUE or FALSE. It is true, or it
ain't. When it's true, you get the formatting -- that's the job of
Conditional Formatting.


--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"MOP" wrote in message
...
This worked perfectly. Thank You. Is there a place (or document) that
will
give me common formulas?

TIA-MOP

"Earl Kiosterud" wrote:

MOP,

Select your cells. Note the active (white) cell of your selection.
FOrmat - Conditional Formatting. Change "Cell value is" to "Formula
is."
Paste this in (from here):

=A2<DATE(YEAR(TODAY())-2,MONTH(TODAY()), DAY(TODAY()))

Change A2 to the active cell of your selection. Click "Format" and
choose
the formatting you want for the older years. OK your way out of it.
This
will give you the formatting

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"MOP" wrote in message
...
I would like a date to turn red when older then 2 years? I don't know
much
about Excel. Thank you,

Using Conditional Formatting for Dates
 

In , Earl Kiosterud
spake thusly:

DATE(YEAR(TODAY())-2,MONTH(TODAY()), DAY(TODAY())) returns a date
two years ago.

What's wrong with

=TODAY()-365*2

unless you're really worried about leap years.

-dman-

Using Conditional Formatting for Dates
 

dman,

That'd work fine, as long as the occasional day or so error doesn't matter,
as you said. The average year is (almost) 365.25 days with leap years. My
formula goes to the exact date for two years ago, that's all.

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Dallman Ross" <dman@localhost. wrote in message
...
In , Earl Kiosterud
spake thusly:

DATE(YEAR(TODAY())-2,MONTH(TODAY()), DAY(TODAY())) returns a date
two years ago.

What's wrong with

=TODAY()-365*2

unless you're really worried about leap years.

-dman-