Forum Discussion
Conditional Formatting
Hello Carol!
This is a wonderful use case for conditional formatting! Here is how I would go about doing it:
I assume all of the due date fields are in the same column? It's easiest to do this if you can select all the cells at once to apply the formatting.
1. Select all the cells you want to be changed with conditional formatting. (In this case, all of the date cells. Let's pretend that the dates are all in column E, so I click and drag to select cells E2:E200)
2. On the ribbon, on the Home tab, click "Conditional Formatting."
3. Click "New Rule" to make your own rules.
4. Click "Use a formula to determine which cells to format."
5. In the "Format values where this formula is true" box, you'll type the formula that you want the formatting to follow. Conditional formatting formulas take a different format than regular formulas and functions. You start with an equal sign, then type an expression that will either return a TRUE or FALSE. I'll show you below one option for your cases.
6. After typing in the box, you can select the format for the cell by clicking on the "Format..." button. Since you want cell fills, click the "Fill" tab, then select the color and hit "Ok."
You'll need to make three separate rules for your three cases. Here are the formulas and the fill colors I would use for your three rules:
Since cells are white by default, I don't think you actually need a rule for today's date - instead I would just make sure that no other rule colors it when today is the day. However, if you wanted to make a rule for it, this would go in the box (substitute E2 for your first cell):
=E2=TODAY()
Fill Color: White
For your second condition
=AND(7>=(TODAY()-E2),(TODAY()-E2)<0)
Fill Color: Yellow
For your third condition, I'm assuming it to be when the task is late (because we set today's date to be white"
=E2>TODAY()
Fill color: Red
Good luck and let us know if it works for you!
- Carol BuffingtonOct 16, 2017Copper Contributor
Good morning,
Well I tried plugging in the formulas that you suggested and it worked, sort of. I skipped the first condition because it wasn't necessary. The third condition formula worked like I needed it to. But the second condition is coloring the cells yellow before it reached the 7 day mark. I have attached a screen shot to show you what I mean. I double checked to make sure I wasn't missing anything in the formula and its just as you wrote it. My co-worker says it's possessed. Any ideas?
Thanks,
Carol
- Bryant BoyerOct 16, 2017Brass Contributor
The mistake was mine Carol! Try using this for the second formula instead:
=AND(7>=(C2-TODAY()),(C2-TODAY())>0)
- Carol BuffingtonOct 16, 2017Copper Contributor
Perfect! Thank you so much!