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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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!
ASKER
That works for me! Thanks!
ASKER