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 :p 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()-A3180 and =TODAY()-A3<360 goes amber (I have also tried this using 181 and <359 and have also tried using Formula is =TODAY()-A3180<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.

Many thanks in advance for all of your help

Lil P xxx

It's hard to be sure without an example file, but how about the below as Condition 3?

=AND(TODAY()-A3180,TODAY()-A3<360)

As a "Formula is" type...