Need Help With CONDITIONAL FORMATTING

[kbdavis]

At work I use Excel 2003, and I was wanting to know if it was possible to highlight a column of cells in red if it was within a certain date range.

I am an account manager where I work, and I have timely filing deadlines I have to meet. I was wanting to know if it was possible to highlight a date in red (all dates are in a column on its own) if the account is between 10-12 months from the original date of service.

So, for example: today being July 1, 2013, any of the accounts that are within 60 days after and including today that are coming up on a year (accounts with a date of service of July 1 - August 31, 2012) needs to be highlighted in red so I know those accounts are almost a year old.

To make this work better, if it's possible to fill the cell of accounts that are 30 days or less of being a year old would be red and the accounts that are 31-60 days of being a year old be filled in yellow.


If you need any more explanation, just ask me and I will do my best to clarify. Thanks for the help!

Note: It is not necessary that it has to be exactly 30 days or exactly 60 days. If it is reasonably close, that will work.

[kbdavis]

Here is a formula (well, an idea of a formula) that I was thinking about... If I could just get some assistance with making it work on Excel with the correct arguments that would be helpful.

IF(330-days>D:D>365-days,color=red,IF(300-days>D:D>329-days,color=yellow,'no color change'))

Now, if someone could just help me turn that garbage above into an actual formula.

So what the 'formula' above is saying is:

If column D is within 330-365 days, the color should be red, if not then next step: If column D is within 300-329 days, the color should be yellow, if not then next step... If neither criteria is met, then there should be no color change...

[FDibbins]

Hi and welcome to the forum

using 2003, under Format, select Conditional Formatting
then in the CF window...
select Between and then use this formula...
=TODAY()-DATE(0,10,0) and =TODAY()-DATE(0,12,0) format fill RED
You may need to play with the ,10, and ,12, to get the results you want

You can use the same principal for the 30-day rule

[kbdavis]

Hi and welcome to the forum

using 2003, under Format, select Conditional Formatting
then in the CF window...
select Between and then use this formula...
=TODAY()-DATE(0,10,0) and =TODAY()-DATE(0,12,0) format fill RED
You may need to play with the ,10, and ,12, to get the results you want

You can use the same principal for the 30-day rule

Thanks for the reply! I will give this a shot tomorrow.. And I am assuming I should just be able to create a new rule for the 31-60 days for the yellow.

EDIT: I just tried on my excel at home and it looks like it works. So, I don't believe that I will have any issues tomorrow. That has solved my problem

[FDibbins]

Happy to help and thanks for the feedback