Conditional Formatting - Can't get it to work, please help

LittlePhoenix

New Member
Joined
Jul 19, 2012
Messages
1
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()-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
frown.gif
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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
Hello,

Perhaps try adding brackets like this:

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

So your condition would be:

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

Watch MrExcel Video

Forum statistics

Threads
1,127,753
Messages
5,626,667
Members
416,199
Latest member
Gautamsunil

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top