Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
conditional formatting - dates
Hi
I'm using Excel 2007 and would like to know how to conditionally format dates so the color changes as per: Green - the last 3 days Orange - 4 to 7 days old Red - older than 7 days Is there a single formula that can do this under the conditional formatting or do I need to do 3 rules? Cheers |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
conditional formatting - dates
You need at least 2 rules. You could set one format as the default (say
red). Then CF1: Formula is =(A1-TODAY())<=3 Format1: <green CF2: Formula is =(A1-TODAY())<=7 Format2: <orange or you could leave the cell(s) formatted as general and add the third CF: CF3: Formula is =ISNUMBER(A1) Format3: <red In article , Roger wrote: Hi I'm using Excel 2007 and would like to know how to conditionally format dates so the color changes as per: Green - the last 3 days Orange - 4 to 7 days old Red - older than 7 days Is there a single formula that can do this under the conditional formatting or do I need to do 3 rules? Cheers |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
conditional formatting - dates
Thanks for the help.
Your formula works if rearranged to =(today()-A1)<=3 etc as the today value will always be the highest in my application. If I have a column with cells formatted to 'custom d mmm', then enter just the number of the day rather than the full blown 'day/month' entry (ie 18/10 for 18th Oct), the conditional formatting will not pick this up! Conditional formatting seems to respond to keyboard entry of 'day/ month', and seems to disregard the underlying cell formatting setup with 'custom'. Am I missing something here? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
conditional formatting - dates
Hi,
In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, Manage Rules, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: 1st condition =A2<TODAY()-7 2nd condtion =A2<TODAY()-3 3rd condtion =A2<TODAY() 5. For each of these Click the Format button and choose a format. 6. Between each of these Click OK twice and click New Rule 7. Make sure the rules are in the order stated above, and turn on the Stop If True boxes for the first two rules. Then click OK. You may need to adjust the formulas based on how you define the last 3 days (does it include today or not?) This assumes your dates are in A2:A20, adjust accordingly. To your latest question: If you are using US system dates then an entry of 18/10 is treated as text by Excel. If you enter it as 10/18 then Excel will assume Oct 18th of the current year. So there is nothing wrong with the condtional formatting only the way you are entering the dates. Just enter them as month/day and format them any way you want. By the way formatting 18/10 to d/mmm has not effect at all on text entries, but it will on legal Excel dates. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Roger" wrote: Thanks for the help. Your formula works if rearranged to =(today()-A1)<=3 etc as the today value will always be the highest in my application. If I have a column with cells formatted to 'custom d mmm', then enter just the number of the day rather than the full blown 'day/month' entry (ie 18/10 for 18th Oct), the conditional formatting will not pick this up! Conditional formatting seems to respond to keyboard entry of 'day/ month', and seems to disregard the underlying cell formatting setup with 'custom'. Am I missing something here? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting with Dates | New Users to Excel | |||
Conditional Formatting Dates | Excel Discussion (Misc queries) | |||
Dates in Conditional Formatting help!!! | Excel Worksheet Functions | |||
Conditional Formatting with Dates | Excel Worksheet Functions | |||
Conditional Formatting Dates | Excel Worksheet Functions |