create a conditional formatting based on a date range identified in a specific column

Question

I am trying to create a conditional formatting command that identifies to date ranges based on a specific date.

Reference date found in B4 (02/05/2024)

Green formatting for dates between 150 to 240 days based on date entered in B4 (07/04/2024-10/02/2024)

Orange formatting for dates less 149 days and greater than 92 days, < 07/03/2024 or >05/07/2024 and dates between 241 and 256 days.

Anything beyond B4 + 256 days would be red.

Answer 1

To create conditional formatting based on the specified date ranges in Excel, you can use the following steps:

1. Select the range where you want to apply the conditional formatting.
2. Go to the Home tab, click on Conditional Formatting, and then select New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter the following formulas for each condition:
   • Green Formatting (for dates between 150 to 240 days):
     =AND(A1>=B4+150, A1<=B4+240)
     (Replace A1 with the first cell in your selected range.)
   • Orange Formatting (for dates less than 149 days and greater than 92 days):
     =OR(AND(A1<B4+149, A1>B4-92), AND(A1>=B4+241, A1<=B4+256))
   • Red Formatting (for dates beyond B4 + 256 days):
     =A1>B4+256
5. After entering each formula, click on the Format button to choose the desired formatting (Green, Orange, or Red).
6. Click OK to apply the rules.

Make sure to adjust the cell references according to your specific worksheet layout. This method will allow you to visually differentiate the dates based on the conditions you specified.

---

References:

• Set conditional formatting for cross-column comparisons
• Custom conditions

Answer 2

Use formula based conditional formatting with your selected date range (for example, if your dates are in D:D, select that range first, then create rules using D1 as the first cell in the selection).

Assuming:

Reference date = $B$4

Date to evaluate = the first cell in your selected range (example: D1)

1. Green (150 to 240 days after B4)

=AND(D1<>"", D1>=$B$4+150, D1<=$B$4+240)

2. Orange (93 to 149 days OR 241 to 256 days after B4)

=AND(D1<>"", OR(AND(D1>=$B$4+93, D1<=$B$4+149), AND(D1>=$B$4+241, D1<=$B$4+256)))

3. Red (more than 256 days after B4)

=AND(D1<>"", D1>$B$4+256)