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!