#### LittlePhoenix

##### New Member
Hello everybody!!!

Please bear with me as I am totally new to all of the forum stuff and I really need an expert opinion on where I may be going wrong with my conditional formatting - please see if you can spot my (obvious?) mistake I thought that this would be the best place to come for some good advice as I am now running out of ideas!!

Basically, I have been tasked to maintain a spreadsheet that holds all of the dates of vaccinations and security clearances etc. My colleague has already created the s/s he would like to use, containing the relevant information (unfortunately I cannot post it here so will have to try and explain). He does not want any additional rows or columns of information containing values, he just wants conditional formatting applied to the date cells to flag up when a date has passed a year, is less than 6 months and is between 6 mths to a year. He wants the formatting applied to future dates and dates retrospectively.

If I am looking at the dates retrospectively he would like any cells that are greater than 360 days to go red, any cells that are less than 180 days to go green and anything between 360 and 180 days to go amber. So far I have got the red and green argument to work perfectly by using:

Condition 1 - Formula is =TODAY()-A3>=360 goes red
Condition 2 - Formula is =TODAY()-A3<=180 goes green
Condition 3 - Cell value between =TODAY()-A3>180 and =TODAY()-A3<360 goes amber (I have also tried this using >181 and <359 and have also tried using Formula is =TODAY()-A3>180<360 and othe variants)

It doesn't matter what formula I use i.e. TODAY, NOW, DAYS360 or which way round I do this it will not format the cells to go amber!! As I said, red and green are absolutely fine and as soon as the days tick over on the cells that should be amber i.e >360 or <180 they go green or red??!!

If I add an extra column that gives me the value of TODAY()-A3 and apply the formatting to the cell values it all works fine, amber and all!!

Am I making an obvious error here? I am fairly new to excel and if I could just do the above it would be fine but my colleague is adamant we just apply all arguments to the date cell. I can see where he is coming from as it is so much tidier and easy to look at - I just can't for the life of me figure where I am going wrong
I'm assuming there is something conflicting in the formula I am using?

I also need to apply this same criteria to dates in the future - i.e flagging up if it is less that 6 months to go red, more than a year to go green and anything in between to go amber!!

I really hope this makes sense and I apologise in advance if I am being a bit of a miffy..............just don't have a clue where to go from here.

Lil P xxx

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### circledchicken

##### Well-known Member
Hello,

Perhaps try adding brackets like this:

=(TODAY()-A3)>180 and =(TODAY()-A3)<360

=AND((TODAY()-A3)>180,(TODAY()-A3)<360)

Last edited:

Replies
8
Views
293
Replies
20
Views
313
Replies
8
Views
106
Replies
1
Views
35
Replies
15
Views
330

1,126,902
Messages
5,621,569
Members
415,846
Latest member
Jrider1

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