Fill color by date

[Intellphantom]

I'm working with Exel 2007. I made a simple spreadsheet with several due
dates of a particular project. The spreadsheet shows when a person first came
onto the company, and I need to track when their evaluations are due. The
first is due 30 days after, 10 months after and a year after the initial hire
date. I'd like to be able to turn the cell yellow two weeks before the review
date, and red on or after the due date. I'm somewhat familiar with Excel, but
it's been several years since I've had to write formulas. Thank you for your
time!!

 

[Barb Reinhardt]

You'd use Conditional Formating to do this. I don't have Excel 2007 here to
step you through how to do it in 2007.

For a formula, you'd use

=TODAY()-$A1 <=10

Where the start date is in A1.

 

[Bernie Deitrick]

Intellphantom,

Use Conditional Formatting with formulas. This assumes that your date is in cell A2: select A2 and
use Format / Conditional Formatting... Formula Is

Condition 1: (set the fill to yellow)
=OR(AND(TODAY()>DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)-14),TODAY()<=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))),AND(TODAY()>DATE(YEAR(A2),MONTH(A2)+10,DAY(A2)-14),TODAY()<=DATE(YEAR(A2),MONTH(A2)+10,DAY(A2))),AND(TODAY()>DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)-14),TODAY()<=DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))))

Condition 2: (set the fill to red)
=OR(AND(TODAY()>DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)),TODAY()<=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)+14)),AND(TODAY()>DATE(YEAR(A2),MONTH(A2)+10,DAY(A2)),TODAY()<=DATE(YEAR(A2),MONTH(A2)+10,DAY(A2)+14)),AND(TODAY()>DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)),TODAY()<=DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)+14)))


HTH,
Bernie
MS Excel MVP

 

[Intellphantom]

Wow, that's awesome! Thank you so much for taking the time. I've been going
crazy. So, since the 30 day feedback is in one column, the 10 month is in
another & the yearly is in a third, I'd just break down the formula to
reflect this, correct?

=OR(AND(TODAY()>DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)-14),TODAY()=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)))

=OR(AND(TODAY()>DATE(YEAR(A2),MONTH(A2)+10,DAY(A2)-14),TODAY()=DATE(YEAR(A2),MONTH(A2)+10,DAY(A2)))
etc...

Now, so I'm not lost next time, what exactly in this formula, and the
following, turn the cells a different color?

 

[Bernie Deitrick]

Since you are splitting the feedback into three parts, there is no need for the OR wrapper:

=OR(AND(TODAY()>DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)-14),TODAY()=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)))

Should be:

=AND(TODAY()>DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)-14),TODAY()=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))

etc....

When you are writing formulas for CF, you simply need a formula that returns TRUE or FALSE based on
some condition - you can refer to the cell value, other cell values, etc. The formulas I wrote just
look for dates that are within 14 days either side of one month, 10 months, or one year earlier than
today - that is the AND part.

HTH,
Bernie
MS Excel MVP

 

[Intellphantom]

Nevermind to the last question. Again, haven't worked with Excel in a while.
The first question still stands. Also, how do I acutally INPUT the formula?
When I tried putting it in w/ the words (Date(Year...etc) it gives me an
error, I try taking them out & it gives me an error yet again. This is how I
currently have it...

=OR(AND(TODAY(08/19/08)>DATE(YEAR(B6),MONTH(B6)+1,DAY(B6)-14),TODAY(08/19/08)<=DATE(YEAR(B6),MONTH(B6)+1,DAY(B6)))

 

[Bernie Deitrick]

TODAY doesn't take an argument - leave it as TODAY() - it will automatically update the date every
day - which is what you want.

=AND(TODAY()>DATE(YEAR(B6),MONTH(B6)+1,DAY(B6)-14),TODAY()<=DATE(YEAR(B6),MONTH(B6)+1,DAY(B6))


HTH,
Bernie
MS Excel MVP

 

[Intellphantom]

Mr. Deitrick,

I have the formulas re-adjusted as you told me. When entering them into the
conditional formatting area, the cell doesn't seem to respond. I'm on the
cell that should change colors, C6. I click on CF, Manage Rule, Edit Rule,
Use a formula to determine which cells to format, I enter formula & format it
to yellow & click ok. I do the same for the red. When I enter a date,
however, it doesn't reflect the yellow, or red fill color. What am I doing
wrong?

 

[Bernie Deitrick]

Which version of Excel are you using?
Post your email address - put in spaces and change the @ to at to fool spam address harvesters - and
I will send you a working version.

HTH,
Bernie
MS Excel MVP

 

[Intellphantom]

I'm using 2007.
wendy.alaniz at goodfellow.af.mil

 

[Bernie Deitrick]

Wendy,

Sent....

HTH,
Bernie
MS Excel MVP

I'm using 2007.
wendy.alaniz at goodfellow.af.mil

 

[Intellphantom]

Not sure why I didn't get it. Try sending it to wendy.alaniz at gmail.com,
please.

 

[Bernie Deitrick]

Wendy,

Will do. Many email servers automatically reject attachments for security
purposes. I will send the file tomorrow, when I have access to the machine
it is stored on.

Bernie

 

[Bernie Deitrick]

Wendy,

I sent both the workbook and another message with no attachment. Let me know if you receive either.

HTH,
Bernie
MS Excel MVP