Conditional Formatting

Taryn A · Jul 5, 2013

I have an Excel workbook with a lot of dates that are colored coded as follows

Red indicates that the date in the cell is 13 months past the date entered in the cell

Orange indicates that the date in the cell is 1 year past the date in the cell

Green indicates its within the year of the date entered in the cell.

As the workbook is set up now i have to manually change the color of the cell.

i know there is a formula using conditional formatting that can automatically change the date but I'm not sure what that formula is. I will need this for most of the workbook. Can anyone help?

etaf · Jul 5, 2013

are you comparing to todays date
if so then you could use today()

Assume in Cell A1

=A1<date(year(today()),month(today())-13,day(today()))
< DATE(YEAR(TODAY()),MONTH(TODAY())-13,DAY(TODAY()))
would show true for a date older than 13 months

=A1<date(year(today())-1,month(today()),day(today()))
< DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))
that older than a year

=A1 > DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))
younger than a year

do a stop if true

you need an = on one of those to include today a year ago - as we have used greater and less than - so depending on which colour you want today exactly a year tobe add
> =
or
< =

Excel Workbook

A

6

01/07/2013

7

22/06/2012

8

01/07/2011

Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6	1. / Formula is =A6	Abc
A6	2. / Formula is =A6	Abc
A6	3. / Formula is =A6>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))	Abc

</date(year(today())-1,month(today()),day(today()))
</date(year(today()),month(today())-13,day(today()))

Taryn A · Jul 8, 2013

etaf said:
are you comparing to todays date
if so then you could use today()

Assume in Cell A1

=A1<DATE(YEAR(TODAY()),MONTH(TODAY())-13,DAY(TODAY()))
< DATE(YEAR(TODAY()),MONTH(TODAY())-13,DAY(TODAY()))
would show true for a date older than 13 months

=A1<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))
< DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))
that older than a year

=A1 > DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))
younger than a year

do a stop if true

you need an = on one of those to include today a year ago - as we have used greater and less than - so depending on which colour you want today exactly a year tobe add
> =
or
< =

Sheet1

* A
6 01/07/2013
7 22/06/2012
8 01/07/2011

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 75px"></COLGROUP><TBODY>
</TBODY>

Conditional formatting

<TBODY>
<TD>

Cell Nr.: / Condition Format
A6 Abc
A6 2. / Formula is =A6<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))< td>[TD="bgcolor: #ffc000"]Abc
A6 3. / Formula is =A6>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())) Abc

<TBODY>
<TD>1. / Formula is =A6<DATE(YEAR(TODAY()),MONTH(TODAY())-13,DAY(TODAY()))< td>
</TBODY>
[/TD]

</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

</DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))
</DATE(YEAR(TODAY()),MONTH(TODAY())-13,DAY(TODAY()))[ QUOTE]

Taryn A · Jul 8, 2013

Thanks so much for replying! Actually the date varies depending on when the physical was done. So it won't be "today's" date. Does that make sense?

etaf · Jul 8, 2013

so instead of using today() just replace with the cell the date is in that you want to do the comparison with to trigger the colours

Taryn A · Jul 8, 2013

etaf said:
so instead of using today() just replace with the cell the date is in that you want to do the comparison with to trigger the colours

Okay, I'm sorry for being so ignorant, but I'm trying to get this right...As an example, in one of the cells I have a test date of 3/26/10; based on today's date (current date) this cell should be coded red since the test date has exceeded 15 months. In another cell, I have a test of 4/24/12; based on today's date, this test date should be coded orange since the test date has exceeded 12 months. In another cell I have a test date of 4/17/13; based on today's date this cell should be coded green as it does not exceed 12 or 15 months from the test date of 4/17/13 entered in that particular cell.

I would like for the workbook to update in real-time. Is there something special I need to do to have that occur?

Do I have to enter the conditional formatting into each cell or can I highlight the cells I want to apply the formatting to so that I don't have to do it for each individual cell? Am I making any sense?

Thank you so much for your help and patience with me!
</SPAN>

Taryn A · Jul 8, 2013

I apologize in advance if this gets posted twice, but when I hit submit reply it indicated I was logged out.

Okay, I'm sorry for being so ignorant, but I'm trying to get this right...As an example, in one of the cells I have a test date of 3/26/10; based on today's date (current date) this cell should be coded red since the test date has exceeded 15 months. In another cell, I have a test of 4/24/12; based on today's date, this test date should be coded orange since the test date has exceeded 12 months. In another cell I have a test date of 4/17/13; based on today's date this cell should be coded green as it does not exceed 12 or 15 months from the test date of 4/17/13 entered in that particular cell.

I would like for the workbook to update in real-time. Is there something special I need to do to have that occur?

Do I have to enter the conditional formatting into each cell or can I highlight the cells I want to apply the formatting to so that I don't have to do it for each individual cell? Am I making any sense?

Thank you so much for your help and patience with me!
</SPAN>

etaf · Jul 9, 2013

you said in the orginal post

Red indicates that the date in the cell is 13 months past the date entered in the cell

now you are saying

As an example, in one of the cells I have a test date of 3/26/10; based on today's date (current date) this cell should be coded red since the test date has exceeded 15 months.

so instead of using -13 , you need -15
and put in the condition format in Red/Orange/Green order and stop if true

cell A6 = 3/26/10 - RED - 15 months
cell A7 = 4/24/12 - orange - 12 months
cell A8 = 4/17/13 - green - less than 12 months

see here is your examples and the formula used for conditional formatting

you can highlight all the cells you need to add the format to

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

2

A1 DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))younger than a year

3

4

5

6

26/03/2010

cell A6 = 3/26/10 - RED - 15 months

7

24/04/2012

cell A7 = 4/24/12 - orange - 12 months

8

17/04/2013

cell A8 = 4/17/13 - green - less than 12 months

Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6	1. / Formula is =A6	Abc
A6	2. / Formula is =A6>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))	Abc
A6	3. / Formula is =A6	Abc

Taryn A · Jul 9, 2013

Sorry for the confusion! Thanks for you help!

etaf · Jul 9, 2013

Thats OK - does it work for you now ?

Conditional Formatting

Taryn A

New Member

Excel Facts

etaf

Well-known Member

Taryn A

New Member

Taryn A

New Member

etaf

Well-known Member

Taryn A

New Member

Taryn A

New Member

etaf

Well-known Member

Taryn A

New Member

etaf

Well-known Member

Similar threads

Forum statistics

Share this page

Cell	Nr.: / Condition	Format
A6	Abc
A6	2. / Formula is =A6<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))< td>[TD="bgcolor: #ffc000"]Abc
A6	3. / Formula is =A6>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))	Abc

Conditional Formatting

New Member

Excel Facts

Well-known Member

New Member

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock