| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating based on Date
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating based on Date
Change condition 1 to:
=AND(A1<=TODAY(),A10) Regards, Per On 7 Mar., 21:47, 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating based on Date
Now blanks are showing yellow. Please help.
"Per Jessen" wrote: Change condition 1 to: =AND(A1<=TODAY(),A10) Regards, Per On 7 Mar., 21:47, 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. . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating based on Date
Hi Kimti,
Try this to see if works: condition 1: =AND(A1<=TODAY(),A1<"") condition 2: =AND(A1-TODAY()<10,A1<"") Click yes below if it works for you. "Kimti" wrote: Now blanks are showing yellow. Please help. "Per Jessen" wrote: Change condition 1 to: =AND(A1<=TODAY(),A10) Regards, Per On 7 Mar., 21:47, 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. . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating based on Date
Hi Kimti,
Try this to see if it works for you: condition 1: =AND(A1<=TODAY(),A1<"") condition 2: =AND(A1-TODAY()<10,A1<"") Click yes below if it works. "Kimti" wrote: Now blanks are showing yellow. Please help. "Per Jessen" wrote: Change condition 1 to: =AND(A1<=TODAY(),A10) Regards, Per On 7 Mar., 21:47, 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. . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating based on Date
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating based on Date
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating based on Date
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional formating based on other cells | Excel Worksheet Functions | |||
| Conditional Formating based on Date | Excel Worksheet Functions | |||
| Conditional Formating Based on Date | Excel Discussion (Misc queries) | |||
| Set conditional formating for cell based on value in different cel | Excel Discussion (Misc queries) | |||
| Conditional Formating based on another cell | Excel Discussion (Misc queries) | |||
Hybrid Mode
