Using Conditional Formatting for Dates

M

MOP

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

Earl Kiosterud

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
 
M

MOP

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

TIA-MOP
 
A

Andrew Millman

Earl Kiosterud said:
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
Click to expand...

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
 
E

Earl Kiosterud

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.
 
D

Dallman Ross

Earl Kiosterud said:
DATE(YEAR(TODAY())-2,MONTH(TODAY()), DAY(TODAY())) returns a date
two years ago.
Click to expand...

What's wrong with

=TODAY()-365*2

unless you're really worried about leap years.

-dman-
 
E

Earl Kiosterud

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.
 
Top