Fill color by date

Wendy,

Sent....

HTH,
Bernie
MS Excel MVP


"Intellphantom" wrote in message
...
I'm using 2007.
wendy.alaniz at goodfellow.af.mil

"Bernie Deitrick" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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))),AN D(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)),A ND(TODAY()DATE(YEAR(A2),MONTH(A2)+10,DAY(A2)),TOD AY()=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" wrote in message
...
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!!

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

"Bernie Deitrick" wrote:

Wendy,

Sent....

HTH,
Bernie
MS Excel MVP


"Intellphantom" wrote in message
...
I'm using 2007.
wendy.alaniz at goodfellow.af.mil

"Bernie Deitrick" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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))),AN D(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)),A ND(TODAY()DATE(YEAR(A2),MONTH(A2)+10,DAY(A2)),TOD AY()=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" wrote in message
...
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!!

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

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

"Bernie Deitrick" wrote:

Wendy,

Sent....

HTH,
Bernie
MS Excel MVP


"Intellphantom" wrote in
message
...
I'm using 2007.
wendy.alaniz at goodfellow.af.mil

"Bernie Deitrick" wrote:

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" wrote in
message
...
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" wrote:

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" wrote in
message
...
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" wrote:

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))),AN D(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)),A ND(TODAY()DATE(YEAR(A2),MONTH(A2)+10,DAY(A2)),TOD AY()=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" wrote
in message
...
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!!

Wendy,

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

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" deitbe @ consumer dot org wrote in message
...
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

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