# Conditional Formatting based on dates and days passed

#### R4nd0m4ng3l

##### New Member
Hi,

I am trying to add conditional formatting to my spreadsheet. The sheet records when I receive applications and how long since I received them. I have a column called 'Date Received' and want to add formatting so that the cell changes colour based on how long ago the date is. The parameters I need and below, any help would be great! Thanks in advance!

If the date is 0-28 days ago - Cell is Green
If the date is 29-42 days ago - Cell is Yellow
If the date if 43-56 days ago - Cell is Orange
If the date is over 57 days ago - Cell is Red

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Special-K99

##### Well-known Member
Try (untested)

4 Conditions
In this order

=(A1 < TODAY()-57)
format Red

=(AND(A1 < =TODAY()-43,A1 > =TODAY()-56))
format Orange

=(AND(A1 < =TODAY()-29,A1 > =TODAY()-42))
format Yellow

=(AND(A1 < =TODAY()-0,A1 > =TODAY()-28))
format Red

#### R4nd0m4ng3l

##### New Member
That doesn't seem to work either. Thanks for the suggestion though

#### Special-K99

##### Well-known Member
Works fine for me.
Remove the spaces before/after the carat signs.

Clear any conditional formulas you have for A1
Select A1
Enter the 4 conditional formulas for A1
in A2 enter =TODAY()-A3

in A3 enter 58
A1 turns red its greater than 57 days

in A3 enter 44
A1 turns orange its between 43 and 56 days

in A3 enter 31
A1 turns yellow its between 29 and 42 days

in A3 enter 17
A1 turns green its between 0 and 28 days

#### R4nd0m4ng3l

##### New Member
I can get it to work on the one cell, is there a way I can then make it work for the whole column?

#### Special-K99

##### Well-known Member
Use Format Painter (the paintbrush icon) to copy to other cells

#### R4nd0m4ng3l

##### New Member
Excellent! Thank you!

Replies
1
Views
350
Replies
1
Views
120
Replies
3
Views
435
Replies
9
Views
233
Replies
2
Views
245

1,190,787
Messages
5,982,920
Members
439,807
Latest member
WXM86

### 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