Hi there,

I’ve got a project on at the minute, where I need to have cells containing dates change between colours under 2 conditions. (Technically 3 but I’ve already sorted the last one out.)

The dates are split into 2 columns, one for the date the source is deleted, and the other is when the source document should be downloaded by. The source deletion date is 6 years after the source date, and the source download by date is 2 years prior to the deletion date.

My requirements are as follows:

Orange

When the source deletion date is within 4 years and 25 days of today’s date.When the source download date is within 2 years and 25 days of today’s date.

Red

When the source deletion date is within 4 years of today’s date.When the source download date is within 2 years of today’s date.

Basically, I want a 25 day window where both cells in D and E go from white to orange – showing it needs to be tasked, and then orange to red – showing that the 25 day window has passed.

See example below:

I’ve spent a good time trying to work out the cell formatting for this, and just can’t get it to work. Either it stays orange, or doesn’t pick it up at all when I test it with different dates.

Any help would be appreciated, and step-by-step would also help as I’m still new to this type of Excel.

P.S. In my actual sheet, I’ve got a formula in the D and E columns to pick up the date in C, and add 6 years and 4 years respectively.

I’ve also already got a rule for the green colour whereby if “Yes” is noted in G column, then it will mark all dates as green (completed) regardless of today’s date.

​Hi there, I’ve got a project on at the minute, where I need to have cells containing dates change between colours under 2 conditions. (Technically 3 but I’ve already sorted the last one out.) The dates are split into 2 columns, one for the date the source is deleted, and the other is when the source document should be downloaded by. The source deletion date is 6 years after the source date, and the source download by date is 2 years prior to the deletion date. My requirements are as follows:OrangeWhen the source deletion date is within 4 years and 25 days of today’s date.When the source download date is within 2 years and 25 days of today’s date.RedWhen the source deletion date is within 4 years of today’s date.When the source download date is within 2 years of today’s date.Basically, I want a 25 day window where both cells in D and E go from white to orange – showing it needs to be tasked, and then orange to red – showing that the 25 day window has passed. See example below: I’ve spent a good time trying to work out the cell formatting for this, and just can’t get it to work. Either it stays orange, or doesn’t pick it up at all when I test it with different dates. Any help would be appreciated, and step-by-step would also help as I’m still new to this type of Excel. P.S. In my actual sheet, I’ve got a formula in the D and E columns to pick up the date in C, and add 6 years and 4 years respectively.I’ve also already got a rule for the green colour whereby if “Yes” is noted in G column, then it will mark all dates as green (completed) regardless of today’s date.   Read More

​