Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting a range with conditional formatting | Excel Worksheet Functions | |||
conditional formatting glitches | Excel Discussion (Misc queries) | |||
Keeping conditional formatting when sorting | Excel Discussion (Misc queries) | |||
conditional formatting | Excel Discussion (Misc queries) | |||
cannot use ISEVEN or ISODD functions in Conditional Formatting | Excel Worksheet Functions |