Solved how can use color code with dates?

September 27, 2015 at 00:04:44
Specs: Windows 7
Hi, I have two columns in excel with dates. Column B is predefined with different dates. Columns C is to fill by us after doing perticular work.
Now, I want that if Column C's date is greater then or less then 3 dates then column B's date, cell color would be change with Red color, if value is in between it then cell color would be green and if cell is blank then it would be yellow.

Please help.

message edited by PriteshPatel


See More: how can use color code with dates?

Report •

#1
September 27, 2015 at 08:18:00
Look at this HOW-TO

http://www.computing.net/howtos/sho...

It explains how to use Conditional Formatting with Past Due Dates,
it uses the TODAY() function, but it can be replaced by any cell reference.

After reading it, if you have any problems, or question, please ask.

MIKE

http://www.skeptic.com/


Report •

#2
September 28, 2015 at 13:26:20
No, No, No, Let me give a example.


Column A Column B Column C Column D

11.09.2015 E-RTG-HT-05 MECH PM OF GANTRY-MONTHLY 18.09.2015
11.09.2015 E-RTG-HT-05 MECH PM OF TROLLEY-MONTHLY 10.09.2015
12.09.2015 E-RTG-HT-06 MECH PM OF ROPE LUBRICATION 13.09.2015
12.09.2015 E-RTG-HT-06 ELECT PM OF E-ROOM-MONTHLY 05.09.2015
13.09.2015 RMQC-02 ELECT PM OF HV SLIP RING-
13.09.2015 RMQC-02 ELECT PM OF AUX. TRANSFORMER 12.09.2015

I have these four columns. Column A's dates are predefined. Column D's Date are entered by us after finishing Task. Now I want that- If Column D's date is greater then or less then three dates of Column A's Date color would be RED. If D's Date is in-between three dates of A's date color would be GREEN. If D's Cell is Blank color would be YELLOW.
According to example, 18.09.2015 should be RED,
10.09.2015 Should be GREEN,
13.09.2015 should be GREEN,
05.09.2015 should be RED,
Blank should be YELLOW,
12.09.2015 should be GREEN.

message edited by PriteshPatel


Report •

#3
September 28, 2015 at 17:10:26
✔ Best Answer
See if this works for you:

This is for Excel 2007
There are Three separate formulas,
and they must be in the correct order,
so after your done entering all the formulas
they should be, in the following color order:

Yellow
Red
Green

First Formula:

1) Select your cell or Range of Cells: D1 - D6
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =AND($D1<=MAX($A$1:$A$6),($D1>=MIN($A$1:$A$6)))

6) Click on the Format button
7) Select the Fill Tab
8) Select a Green color
9) Click OK
10) Click OK

Second formula:

1) Select your cell or Range of Cells, should be the same as above
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =OR($D1>MAX($A$1:$A$6),($D1<MIN($A$1:$A$6)))

6) Click on the Format button
7) Select the Fill Tab
8) Select a Red color
9) Click OK
10) Click OK

Third Formula:

1) Select your cell or Range of Cells, should be the same as above.
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =D1=""

6) Click on the Format button
7) Select the Fill Tab
8) Select a Yellow color
9) Click OK
10) Click OK

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
September 28, 2015 at 18:53:55
After thinking about this, to shorten things up a bit you can
change the GREEN formula to: =D1<>""
and it should work.

MIKE

http://www.skeptic.com/


Report •

Ask Question