Specialized conditional formatting

[Davorah]

Hey there Excel World!

I am having a problem with formatting my Due Dates column to fit my parameters.

I need to make the font color change as follows:

7 days Before the due date listed to be Yellow font.

3 days Before the due date listed to be Red font.

---

The pre-existing options for what I need (Con. For. -> Highlight cell rules -> A date occuring) allow only for formatting to "Today, Tomorrow, In the last 7 days, last week, etc." with its drop down menu.

I have reviewed the other thread about excel due date formatting but those formulas provided are not working for me to accomplish what I need....

Please help!

- Davorah Stetson
Co-Founder of FashionGun.com & Curator of the FashionGun Style House.
Freelance Admin Professional and Mixologist Extraordinaire

"If you practice anything for 10,000+ hours, consecutively or not, you are considered an expert." - Gina Stark

[K m]

Not sure what you need. Is this what you need?

Condition 1
In the appropriate cell say cell A1
=A1=today()+7 YELLOW FONT

Condition 2
=A1=today()+3 RED FONT

[Davorah]

Unfortunately those formulas arent working for me... o.O

[Special-K]

Condition 1
In the appropriate cell say cell A1
=(A1=today()+7) YELLOW FONT

Condition 2
=(A1=today()+3) RED FONT

Note the brackets after TODAY
and the fact there are no quotes in the conditions

[K m]

Your formula does not match mine.

- Delete the quotes (") around the formula
- the Today formula is =TODAY() not TODAY. The parenthases matter

Conditional formatting is based on a TRUE FALSE result. Your formula does not result in either TRUE OR FALSE. In this case, you want to compare the current cell with a future date. That's why I used A1=today()+7

[Davorah]

Ok,

here is my adjustments attached...

PROBLEM EXCEL.jpg

Heres a better description of what Im trying to accomplished:

I need the 8/8/12 (since it is Today) + 3 days before 8/8/12 to be red (8/5/12 - 8/8/12)

I need the 8/5/12 + 7 days before 8/5/12 to be yellow (7/29/12 - 8/4/12)

And then I need this to work for all the Due Dates in the column... which are different numbers as you can see in the image

[bentleybob]

I would use:
Condition 1 (RED)

Please Login or Register  to view this content.

Condition 1 (YELLOW)

Please Login or Register  to view this content.

and make sure you click Stop if True for Condition 1.

[K m]

Your attachment isn't valid

[Davorah]

Sorry about that.

I edited my post and reloaded the image so it can be viewed.

[K m]

We can't do anything with a JPG. Do you have an EXcel file?

[Davorah]

That fixed it... however, I have a new problem... lol

Some of my 8/8/12's are red and some are still black...

print screen attached

PROBLEM EXCEL.jpg

[bentleybob]

With your clarification, try
Condition 1 (RED, with Stop If True)

Please Login or Register  to view this content.

and Condition 2 (YELLOW)

Please Login or Register  to view this content.

[Davorah]

Im not allowed to share the doc because of sensitive info. I can make a second file with just the dates to practice with/share though... o.O

[bentleybob]

Why is 8/15 yealolow in G33 and red in G34? I thought you were looking BACK in time.

[Davorah]

The task I am attempting to perform is to notify me when I am 3 days and 7 days from the due dates listed... the due dates are NOT all 8/8/12... They range from 7/2012 to 9/2012.....

Per the formulas I have been testing out, it seems that the formulas are hit/miss... Im not sure of what Im doing wrong but its a bit frustrating... lol!

Is the "today" formula correct for what I am trying to accomplish?

[Davorah]

Woot! I fixed it! Thanks everyone for your help! :D

[bentleybob]

With your latest clarification, try
Condition 1 (RED, with Stop If True)

Please Login or Register  to view this content.

and Condition 2 (YELLOW)

Please Login or Register  to view this content.

[K m]

Davorah:

The problem you have is TODAY is volatile. =TODAY() result will not be the same tomorrow. I think you may need a different approach using a macro

A macro can be written that runs daily and can "lock" in the date. Without seeing your data, we are only guessing what you really want. I understand you data is sensitive but both mine and bentlybob address your original question

Perhaps you could rephrase you needs

[bentleybob]

I think the volatility is what is wanted. That is, at a glance, you can tell what's coming due within 7 and then within 3 days of today. Or at least that's my understanding. So if today is the 8th, anything with a due date of 8/9, 8/10, or 8/11 should show in red. Tomorrow, it will be 8/10, 8/11, and 8/12.