Excel Formula

Question

How can I get column E to change colors based on date in column C, 30-60 days (yellow), 60+ days (red), then turn back to black if a date is entered in column D?

Answer 1

Here is the procedure to Conditionally Format the data in your Column E. You will need to enter two rules for the two conditions.

1. Select the cells to be evaluated for the Conditional Formatting. I used E2:E16 in the example below.
2. On the Home ribbon in the Styles section open Conditional Formatting>New Rule>Use a Formula.
3. Enter this formula in the Format Values line for the Yellow rule.

=AND($D2="",TODAY()-$C2<=60,TODAY()-$C2>=30)

SPECIAL NOTE: The selection you make in Column E must begin in the same row that the formula is referring to. If you start your selection in another row rather than Row 2, modify the row numbers to match the first row of your selection. You can, if desired, also include other columns in the range to be formatted.

4. Select the Format button at the lower right of the window and in the Format Cells dialogue select the desired formatting (Yellow). Then click OK and OK.

Repeat steps 1 through 4 with this formula for the Red rule.

=AND($D2="",TODAY()-$C2>60)

Answer 2

If it still doesn't work, reply back with some specific information on what is in the cells in Column D before entering the date. There could be a conflict with something in the cell like a formula. The way the formula is set up it is looking for an empty cell.

If it does work, please mark the question as answered so others can benefit.

Answer 3

The colors were set appropriately using your formula but they did not turn back to black if a date was entered into column D. I think it was the way I previously had it formatted. I will try again on a new spreadsheet. Thank you!