I want to apply conditional formatting (colour a corresponding cell or use symbols [green/orange/red] circles) to a cell My conditions are the following:

If e.g. cell A4 is empty then

• check if date of current cell lies no less than 13 days in the past - if that condition is met -> Format cell Green (or add Green circle in the cell)
• check if date of current cell lies no less than 14 and no more than 27 days in the past - if that condition is met -> yellow (yellow circle)
• check if date of current cell lies more than 27 days in the past - if that condition is met -> red circle.

If cell A4 is not empty, then do not apply any formatting.

I am kinda stumped with the conditional formatting and I don't know how to combine if-statements in the formula for my specific use case.

Any and all help will be greatly appreciated!

Thanks!

CodePudding user response：

For formatting, you can configure it this way:

The formulas are respectively:

• =AND(ISBLANK($A$4), ISNUMBER(D1), TODAY() - D1 <= 13)
• =AND(ISBLANK($A$4), ISNUMBER(D1), TODAY() - D1 <= 27)
• =AND(ISBLANK($A$4), ISNUMBER(D1), TODAY() - D1 > 27)

Explanation:

1. Note that D1 is the top left of the range where the conditional formatting applies. It is important for it to match the formulas.
2. As the formulas for yellow is always true when the formula for green is true, it is important you tick Stop if True like I did (or reverse the order of the formulas).
3. The formatting applies whenever the formula returns true. The conditions are:
   1. $A$4 (note the $ to fix the cell address) is empty (ISBLANK)
   2. The current cell (D1, corresponding to the top left of the range) is a number (dates are internally numbers in Excel).
      This condition among other things, prevents the cell from being formatted when it is empty.
   3. The conditions you have given for the date apply.
      Note that the very last TODAY() - D1 > 27 is unnecessary (if your cell contains a date and is not already colored by green or yellow, then it is bound to be red) but I kept it for consistency.
   4. Finally, you may want to add a condition to ensure the date is in the past (should a date in the future be colored green or yellow?).
      You have not mentioned anything about future dates so I did not but all you have to do is add D1 < TODAY() or D1 <= TODAY() in each formula to make that work.

CodePudding user response：

You can try this:

=IF(ISBLANK(A4),"",IF(TODAY()-current_cell_date>=13,"Green",IF(AND(TODAY()-current_cell_date>=14,TODAY()-current_cell_date<=27),"Yellow","Red")))

and for symbols you can try this:

=IF(ISBLANK(A4),"",IF(TODAY()-current_cell_date>=13,"✔️",IF(AND(TODAY()-current_cell_date>=14,TODAY()-current_cell_date<=27),"