Conditional Formatting Dates in Excel 2010

I have a spreadsheet where Column L contains a Due Date and Column M contains a Date Completed.  I would like to format the Due Date to a red back ground if the date is today. I'd also like to format the Due Date if it is due within 5 days but not today.  Then once it is completed and the Date Completed is entered, the color formatting in Column L will revert to a blank background.
Sandra FrongilloSenior Business AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sandra FrongilloSenior Business AnalystAuthor Commented:
The format for the cell if the Due Date is within 5 days to yellow back ground.
0
spattewarCommented:
check this one.. hope it helps
Cond-1.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Katie PierceCommented:
Create a control cell somewhere in the spreadsheet and use formula "=TODAY()". You can hide this cell if you don't want to see it--just make it exist so that the spreadsheet always knows what day it is.

Next, highlight the range of Due Date cells and click "Conditional Formatting" in the Styles section of the Home Tab at the top.  Click "New Rule".  Select "Use a formula to determine which cells to format".

In the rule description, enter: "=$H$22-$G22<5" (In my spreadsheet, H22 was the "Today" cell--this one you will want the $ before both the H and 22, unless you plan to have the Today cell in every row.  G22 was the Due Date in my spreadsheet.  That one should not have a $ before the 22, so that the formatting could be copied down to rows below.)

Next, select the format, i.e. Yellow Fill.

Now you will need to go through the first few steps to create another New Rule.

This time the description is: "=$H$22<$G22"

Select the format, i.e. Red Fill

Now you will need to go through the first few steps to create another New Rule.

This time the description is: "=$I22>0" (I22 in my spreadsheet was the Date Completed)

Select the format, i.e. White Fill

I tested this with different dates and it worked.  Hope it works out for you!
0
Sandra FrongilloSenior Business AnalystAuthor Commented:
That works for me!  Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.