Conditional Formatting - Due Dates

**clicky555** · 09-27-2011, 09:39 AM

Hi guys,

I am pulling my hair out over this and would be forever in your debt if solution provided.
I have an excel sheet 2003 with one column set with various future/current dates in format DD/MM/YYYY.
These dates contain due dates of annual returns and i'm looking for some formulas to identify when they are due. 3 conditions in total.
1. Cell to highlight YELLOW up to 30 days before cell date
2. Cell to highlight BLUE up to 28 days after cell date
3. Cell to highlight RED over 28 days after.

Can this be done?

Thanking you in advance.

Niall

**Miraun** · 09-27-2011, 09:53 AM

Hi Clicky;

You can use conditional formatting on the column.
You'll need 3 conditions. The =Today() function will bring up the current day to adjust.
If Cell Value is between =Today()+30 and =Today()-1
If Cell Value is Between =Today() and Today()-28
If Cell Value is Less than Today()-28

**JBeaucaire** · 09-27-2011, 09:57 AM

Assuming the dates are in column D:

1) Highlight column D (it's important to know what the first cell in the highlighted range is, for instance D1)

2) Apply the following Conditional Formatting settings:

Condition1: Formula Is: =D1-TODAY()>28
Formats... Pattern: RED color

Condition2: Formula Is: =D1-TODAY()>0
Formats... Pattern: BLUE color

Condition3: Formula Is: =D1-TODAY()>=-28
Formats... Pattern: YELLOW color

**6StringJazzer** · 09-27-2011, 10:01 AM

Assume for this example that the dates are in column A. I also assume that all dates get at least one of these three colors. I suggest making yellow the default color, then you only need two rules. Set all dates in the column to have yellow fill. Then you need the following formulas in conditional formatting:

For blue
=AND(TODAY()>A1-30,TODAY()<=A1+28)

For red
=TODAY()>A1+28

**clicky555** · 08-02-2012, 01:32 PM

Hi Guys,

Really sorry for the delay in thanking you all for dedicating your time and expertise in helping my with the above.
I have got it working and running with it for several months now without any issues. My bosses in work love the sheet and ease of use.

Top marks!