# How to conditional format cell based on date?

#### Generic

##### Board Regular
I can't seem to come up with a formula to change the color of a cell based on the date in the cell. I am looking to have a cell block change color when the date in the cell is 7 days in the future. I'll bet this is simple, I just cant seem to absorb formula formatting.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
I think just (if A1 is the cell):

=(A1)>(Today()+7)

Does that do it?
Alex

something like =CellRef=TODAY()+7

I think just (if A1 is the cell):

=(A1)>(Today()+7)

Does that do it?
Alex

Nope.

I entered the formula into Office 2007 conditional formatting and set it up to color the block yellow, unfortunately it resulted in coloring the cell yellow regardless of the date.

Example: 4/9/09 or 3/2/09 or 3/9/09.

I have excel set up to perform a query on an access database and place that into a second sheet. I have my main sheet look at 4 rows of data from the second sheet and place that data onto my main sheet. I was hoping to set a conditional format so that if a date was coming up in 7 days it would highlight the cell yellow and if the date was 2 days out highlight it red.

Conditional formatting in Excel has more options so its harder to explain, but the formula looks right (maybe it should be >= rather than > )

Here's Office 2007 help online:
http://office.microsoft.com/en-us/excel/HP100739391033.aspx?pid=CH100648451033#10
(under "use a formula to determine which cells to format")

Test the formula in a regular cell first. If it returns "True" or "False" correctly, then the formula is right. Now you only need to apply it with conditional formatting - this way you know if the problem is your formula or getting the conditional formatting to work right. I'm on 2003 right now so I can't walk through the steps here...

If you have trouble still, tell us exactly what cells on the first sheet need the conditional formatting, and if the formula we came up with is working - also the name of the sheet.

Alex

Replies
2
Views
147
Replies
3
Views
128
Replies
1
Views
220
Replies
4
Views
198
Replies
2
Views
210

1,221,127
Messages
6,158,101
Members
451,464
Latest member
Holden3

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back