Format Cell if it has been a week since Date in Left Adjacent Cell

[MatthewTaylor]

I am trying to figure out how to do some conditional formatting based on the cell to the left. Specifically, I want the cell to be highlighted if a certain number of days have passed since the date in the cell to the left.

For context, the purpose of this is to flag to colleagues if it is the correct day for them to do a certain task.

If you look at my example spreadsheet, you will see that there the tasks are broken down by Thursdays and Fridays. Column C represents the start of the progress, so there is no need for formatting there.

However, for column D, I need the cell to highlight if it has been 1 day since the date in column C, and so on. Specifically:

• Highlight Dx if it has been 1 day since the date in Cx
• Highlight Ex if it has been 1 week since the date in Dx
• Highlight Fx if it has been 6 days since the date in Ex
• Highlight Gx if it has been 1 day since the date in Fx
• Highlight Hx if it has been 1 week since the date in Gx
• Highlight Ix if it has been 1 week since the date in Hx

So, in the example spreadsheet, cells D11, E10, G9, H8 and I7 should be highlighted, to indicate that those tasks are due today.

Does anyone know if this is doable? I had someone suggested a solution elsewhere, but it resulted in basically every blank cell being highlighted, which doesn't suit the purpose. Many thanks!

[maclearyj]

Hello, I have attached a copy of your example with the necessary conditional formatting. Hope it helps.

[jeffreybrown]

You can do this with Conditional Formatting

Conditional Formatting

• Highlight applicable range >> =$D$7:$I$15
• Home Tab >> Styles >> Conditional Formatting >> New Rule
• Select a Rule Type: Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =AND(N(C7),LEN(D7)=0)
• Format… [Number, Font, Border, Fill]
• OK >> OK

[MatthewTaylor]

Thanks! This is certainly a good option, and I might just stick with it.

However, the negatives are:
1) The cell remains highlighted, even if I enter a date in the field (indicating that the task is complete).
2) It only seems to highlight the specific day, so if someone were to miss a deadline and check the spreadsheet the next day, the cell would no longer be highlighted.

[MatthewTaylor]

You can do this with Conditional Formatting

Conditional Formatting

• Highlight applicable range >> =$D$7:$I$15
• Home Tab >> Styles >> Conditional Formatting >> New Rule
• Select a Rule Type: Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =AND(N(C7),LEN(D7)=0)
• Format… [Number, Font, Border, Fill]
• OK >> OK

This is also a good option, though it sort of does the opposite to the other solution. Entering a value in the cell makes it no longer highlighted (hooray!) but what is triggering the cells being highlighted in the first place is simply that there is a value to the left, not whether it is the correct date to run the task.

[MatthewTaylor]

Hello, I have attached a copy of your example with the necessary conditional formatting. Hope it helps.

Fixed it! I used your formula as a base, but changed it to <=TODAY. I also added a separate Rule to ensure that any cell with a value in it goes back to normal (no fill).

Thanks for the help!