Excel 2007 : Conditional formatting-What Im trying to do is All dates

[RDOREMUS]

Hello, I needhelp with a spreadsheet in excel 07. I have 4 columns A, B, C, D. Column A is fixed and is updated manually. B is 30 days past due,'C is 60 days past due and D is 90 days past due. All 3 are dates based off of column A. What Im trying to do is
All dates 29'days and before are green, 30-59 days past is yellow, 60-89'days past is orange and 90+ days is red, I've tried a lot of diff combos but nothing fully works, thank you for your help.

[gary28]

Can you clarify what's in the columns? It sounds like the billing date is in column A, but what is in columns B, C, and D?

Wouldn't it be easier to have a formula in column B that subtracts the billing date from the current date (using the TODAY() function) to get its age? Then you could select the cells in column B and apply four rules to the range (assuming the first calculated age is in cell B2):

Select the "Use a formula to determine which cells to format" option in the Edit Formatting Rule dialog box.

The first rule to enter would be for green. The formula would be =B2<30. Apply a green fill format.
The second rule is for yellow, formula is =B2>29. Apply yellow fill.
The third rule is for orange, formula is =B2>59. Apply orange fill.
And the fourth rule is for red, formula is =B2>89. Apply red fill.

Creating the rules in this order will first try to apply red if applicable, then fall through to orange, then to yellow, and finally green.

Let me know if this isn't what you had in mind. But using these formatting rules should get you on the right track.

There's some good content on conditional formatting at Microsoft's Office.com site and also on the Excel blog (although the content was written for Excel 2010, it applies for the most part to Excel 2007):

A training course: Understand data at a glance with conditional formatting

A blog post: Conditional formatting rules simplified

Once you get to the blog, you can also search for "conditional formatting" and find a number of other really good posts from the product team.

[RDOREMUS]

I tried putting your formulas in that order exactly how you said and now the whole column is red. The format i used was =today()b2>89 - red. Is that correct? And to your question its 4 columns b, c d, e.

B- is an inspected date thats entered in manually

c- 30 days after inspected date

d-60 days after inspected date

e- 90 days after

c,d and e all need to change colors in that range. . If that helps

[RDOREMUS]

inspected 30 days 60 days 90 days

27-aug-10 26-sep-09 26-oct-09 25-nov-09
21-aug-09 20-sep-09 20-oct-09 19-nov-09
2-sep-09 2-oct-09 1-nov-09 1-dec-09

heres a sample if what im dealing with.. .the 30, 60 and 90 all are based off of the inspected date and need to change color as todays date becomes within 0-29 days till the 30 day mark, it turns green, when todays date is 30-59 days within it turns yellow, 60-89 days turns orange, and anything 90+ days turns red.

[MarvinP]

OK RDOREMUS
Here is a sample workbook with what I think you want.
I hate conditional formatting as it always takes me at least 10 trial and errors to get it to work.
Maybe one day I'll understand it.

[RDOREMUS]

Thank you, those formulas worked, the only problem i am having now is that i have blank cells in the columns, when i enter in =isblank(a2) it turns some cells clear others dont react and even some cells with dates in them turn clear, what am i doing wrong?

[MarvinP]

Hi,

If you want a "good" answer you need to supply a sample workbook. Click on the "Go Advanced" below the message area and then the Paper Clip Icon above the message area to open a window, allowing an attachment file.

You can save my example, add or edit it to show the problem and then attach it, if you like.

[RDOREMUS]

haha. . heres the sample workbook. . its a big file.

[RDOREMUS]

the file is huge. . but the only columns that matter at BCDE, where CDE are conditionally formatted off of the date in B. oh yea CDE are how many days PAST due from the inspected date in B.

[MarvinP]

See if this does it for you. Perhaps the check box at the end of each Conditional Formatting was a problem?

[RDOREMUS]

I might of mixed up what i said i needed, im sorry if i did. what i need is for all the dates that are 30-59 days PAST the inspected date (B) to turn yellow, all the days that are 60-89 days PAST the inspected date (B) to be orange, and for all the days that are 90 days PAST are to be red. And any dates that are 29 days and more before the date in column(C) are to be g reen. Im really sorry for the confusion its been a long week.