Conditional Formatting - time based

Hello,

I have a spreadsheet where I am tracking a project with different timelines, and I would like to figure out a conditional format to make it highlight certain cells based on the due date.

I would like these cells to get highlighted a certain color when the due date is next week. Perhaps I can have a few different colors depending on the day.

I have attached a spreadsheet with a simple example of two columns.

If the date in Column B is this coming Friday, I would like Column A to be Red.
If the date in Column B is next Friday, I would like Column A to be Yellow.
If the date in Column B is the following Friday, I would like Column A to be Green.
If the date in Column B happens to be further than 2 Friday's out, then I would want Column A to be Blue.

Regardless of the due date, though, I do want Column A to be highlighted if the cell in Column B is not blank; which based on the above, it would be Blue if it was far out in the future.

How can I do this?

Originally Posted by Special-K

Try this

Select A2:A5

Conditional Formatting
New Rule
Use a formula to determine...

=ABS(B2-(TODAY()+7-WEEKDAY(TODAY()+7-6)))<7
format as red

=AND(B2>TODAY()+7,ABS(B2-(TODAY()+7-WEEKDAY(TODAY()+7-6)))<14)
format as yellow

=AND(B2>TODAY()+14,ABS(B2-(TODAY()+7-WEEKDAY(TODAY()+7-6)))<21)
format as green

=B2-ABS(B2-(TODAY()+21-WEEKDAY(TODAY()+7-6)))>=21
format as blue

This unfortunately did not work. Please see updated attached spreadsheet. All the cells are blue, even though they should be either red, yellow and green according to the dates.