Conditional formatting to change cell colour based on date in another cell

Hi all.

I have attached a sample spreadsheet showing what I currently have on the first line and what I want to achieve on the second.

The spreadsheet is a service schedule. When a date is entered in the first box - "First service in week commencing" - it automatically works out the dates of upcoming services. The conditional formatting I have applied means that when the scheduled date gets within 14 days, the "Next service Due in week commencing" cell will change colour; first to yellow, and when the date is 7 days away,it changes to amber.

When a date is entered in the "Date booked in" column, it has the same behaviour as the previous cell, acting as a warning that it is due.

The next column - "Date serviced" - has a reaction to the date entered. If the date is within 7 days of the due date, it will turn green to show compliance with not just my company's policy, but with the law as well.

What I would like to do is have all 3 cells turn green if the date in the "Date Serviced" is within 7 days of the "Next service due in the week commencing" so D9,D10 & D11 as shown on the row below.

I have tried lots of different things, had it working once and accidentally deleted the working formula and haven't been able to replicate the desired result no matter what I try (because I basically don't know what I'm doing!)

Any help would be greatly appreciated. I bet this is such a simple fix but just too dumb to work it out and google is not helping!!!

While having D9:E14 selected, go to conditional formatting and add this rule:
=AND($F9>=$D9,$F9<=$D9+7)
This should get you close anyway.

Thanks Greg. It's definitely going to be along those lines. I was trying loads of different things before - all derived from google - I know it's something to do with the order as well, but not working yet. I wont be deleting this formula either as I just know it's close!

You didn't say what was wrong with it. If it was that column F wasn't being filled, you would just include that column in the "Applies to" of conditional formatting. I've attached a file. Let me know exactly what isn't working correctly. BTW, I only did it for columns D,E, and F. I figured you could copy it to the rest of the columns if it worked for you.

Hi Greg. Sorry about that. I have already set the conditional formatting to turn green when the date is withing 7 days of the due date. That was easy. What I am finding hard to do is to get all 3 cells to turn green when the cell in the third column (5th, depending on your point of view!) is filled with the date being within 7 days of the due date. It's purely for cosmetic purpose as it would look alot nicer and more professional.

So.... end result is this.....

a date for "next service due in week commencing" has a date entered automatically from the initial date.

I then populate the next column - "date booked in" with the date that the service is due.

The next column shows the actual date of the vehicle service, ideally the same as the previous column.


When I enter the date in the column for "date serviced", I would like that cell and the 2 preceding cells to turn green.

Additionally, if I delete the date in the "date serviced" column, I want all 3 cells to revert back to the colour they were previously.

The formula i have used so far dont accomplish this and that's the problem.

Did you look at the file I attached? It does what you're asking, as best I can tell. Give me the scenario where it doesn't work so I can look at.

Greg.....I think my laptop was having a brainfart when I first downloaded that. It works perfectly and all is sweet in the world of me thanks to you knowing what to do. Can't thank you enough for the help you've given. I honestly knew it would be a simple formula but just couldn't see the wood for the trees!!!!!!!

You're welcome - glad it worked for you!