Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Calculation & Conditional Formatting
Hi, I am trying to use conditional formatting to color/highlight a cell but
am stumped. Cell G2 has a date: 18 Jun 08 I need the cell to be colored/highlighted when 1 year has passed. SO, when the spreadsheet is opened today on 7 Aug 09, the conditional formatting sees that more than 1 year has passed since the date in cell G2 so teh cell is highlighted. How can I do this? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Calculation & Conditional Formatting
1. Select the cell G2
2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(G20,DATE(YEAR(G2)+1,MONTH(G2),DAY(G2))<TODAY ()) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "Stumped" wrote: Hi, I am trying to use conditional formatting to color/highlight a cell but am stumped. Cell G2 has a date: 18 Jun 08 I need the cell to be colored/highlighted when 1 year has passed. SO, when the spreadsheet is opened today on 7 Aug 09, the conditional formatting sees that more than 1 year has passed since the date in cell G2 so teh cell is highlighted. How can I do this? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Calculation & Conditional Formatting
Thanks for the quick and correct answer. I do have a question for you,
however. Inyour formula you have cell G20......is that just to ensure cell G2 has a value in it? If so, could it also be something like G2 is not null or whatever that would be? "Jacob Skaria" wrote: 1. Select the cell G2 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(G20,DATE(YEAR(G2)+1,MONTH(G2),DAY(G2))<TODAY ()) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "Stumped" wrote: Hi, I am trying to use conditional formatting to color/highlight a cell but am stumped. Cell G2 has a date: 18 Jun 08 I need the cell to be colored/highlighted when 1 year has passed. SO, when the spreadsheet is opened today on 7 Aug 09, the conditional formatting sees that more than 1 year has passed since the date in cell G2 so teh cell is highlighted. How can I do this? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Calculation & Conditional Formatting
you have cell G20......is that just to ensure
cell G2 has a value in it? Yes. If the cell is empty it will evaluate to 0 and in Excel 0 is less than today so the cell would be highlighted which you don't want. could it also be something like G2 is not null or whatever that would be? Yes. You could also use G2<"" which means: G2 is not equal to blank. But G20 takes fewer keystrokes! -- Biff Microsoft Excel MVP "Stumped" wrote in message ... Thanks for the quick and correct answer. I do have a question for you, however. Inyour formula you have cell G20......is that just to ensure cell G2 has a value in it? If so, could it also be something like G2 is not null or whatever that would be? "Jacob Skaria" wrote: 1. Select the cell G2 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(G20,DATE(YEAR(G2)+1,MONTH(G2),DAY(G2))<TODAY ()) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "Stumped" wrote: Hi, I am trying to use conditional formatting to color/highlight a cell but am stumped. Cell G2 has a date: 18 Jun 08 I need the cell to be colored/highlighted when 1 year has passed. SO, when the spreadsheet is opened today on 7 Aug 09, the conditional formatting sees that more than 1 year has passed since the date in cell G2 so teh cell is highlighted. How can I do this? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting for cell date to equal today's date | Excel Worksheet Functions | |||
conditional formatting a date | Excel Discussion (Misc queries) | |||
Date Calculation in Conditional Formatting | Excel Worksheet Functions | |||
Conditional Formatting Date | New Users to Excel | |||
Conditional formatting of date | Excel Discussion (Misc queries) |