Conditional Formating based on Date
in [General]
|
Prev: How do I merge two cells without deleting data from the cell?
Next: How do I control the light/dark variations Excel 2007 color theme?
From: Kimti on 7 Mar 2010 15:47 I would like to change the cell color based on date. For example if date is today or less color should be red, if date is upto 10 days from today the color should be yellow. I used following: Condition 1: =A1<=today() RED Condition 2: =A1-today()<10 YELLOW it worked ok if I have date in the cell. If the cell is empty, it is still showing me RED color. I want see no formatting if the cell is empty. Thank you in advance for your kind help.
From: Per Jessen on 7 Mar 2010 16:24 Change condition 1 to: =AND(A1<=TODAY(),A1>0) Regards, Per On 7 Mar., 21:47, Kimti <Ki...(a)discussions.microsoft.com> wrote: > I would like to change the cell color based on date. For example if date is > today or less color should be red, if date is upto 10 days from today the > color should be yellow. I used following: > Condition 1: =A1<=today() RED > Condition 2: =A1-today()<10 YELLOW > > it worked ok if I have date in the cell. If the cell is empty, it is still > showing me RED color. I want see no formatting if the cell is empty. > > Thank you in advance for your kind help.
From: Russell Dawson on 7 Mar 2010 16:38 By trial and error I must admit It's all down to the order in which you set the formatting. By the way you'll need to set c/f for white when cell is blank. You have set it to red when less than today which a blank must be. Set c/f in this order Yellow Red White for White =A1="" -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Kimti" wrote: > I would like to change the cell color based on date. For example if date is > today or less color should be red, if date is upto 10 days from today the > color should be yellow. I used following: > Condition 1: =A1<=today() RED > Condition 2: =A1-today()<10 YELLOW > > it worked ok if I have date in the cell. If the cell is empty, it is still > showing me RED color. I want see no formatting if the cell is empty. > > Thank you in advance for your kind help.
From: Kimti on 7 Mar 2010 16:49 Thank you for quick response. I set as you described, but the blank is also showing yellow color. Even past date is showing yellow color. Please provide further help for this. Kimti "Russell Dawson" wrote: > By trial and error I must admit > > It's all down to the order in which you set the formatting. By the way > you'll need to set c/f for white when cell is blank. You have set it to red > when less than today which a blank must be. > > Set c/f in this order > Yellow > Red > White > > for White > > =A1="" > -- > Russell Dawson > Excel Student > > Please hit "Yes" if this post was helpful. > > > "Kimti" wrote: > > > I would like to change the cell color based on date. For example if date is > > today or less color should be red, if date is upto 10 days from today the > > color should be yellow. I used following: > > Condition 1: =A1<=today() RED > > Condition 2: =A1-today()<10 YELLOW > > > > it worked ok if I have date in the cell. If the cell is empty, it is still > > showing me RED color. I want see no formatting if the cell is empty. > > > > Thank you in advance for your kind help.
From: Russell Dawson on 7 Mar 2010 17:07
I've done it again and works ok. Clear all formatting for the range before trying again. I'm using 2007 but for this it shouldn't differ for 2003 as far as I can remember -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Kimti" wrote: > Thank you for quick response. I set as you described, but the blank is also > showing yellow color. Even past date is showing yellow color. Please provide > further help for this. > > Kimti > > "Russell Dawson" wrote: > > > By trial and error I must admit > > > > It's all down to the order in which you set the formatting. By the way > > you'll need to set c/f for white when cell is blank. You have set it to red > > when less than today which a blank must be. > > > > Set c/f in this order > > Yellow > > Red > > White > > > > for White > > > > =A1="" > > -- > > Russell Dawson > > Excel Student > > > > Please hit "Yes" if this post was helpful. > > > > > > "Kimti" wrote: > > > > > I would like to change the cell color based on date. For example if date is > > > today or less color should be red, if date is upto 10 days from today the > > > color should be yellow. I used following: > > > Condition 1: =A1<=today() RED > > > Condition 2: =A1-today()<10 YELLOW > > > > > > it worked ok if I have date in the cell. If the cell is empty, it is still > > > showing me RED color. I want see no formatting if the cell is empty. > > > > > > Thank you in advance for your kind help. |