L
LittlePhoenix
Hello everybody!!!
Please bear with me as I am totally new to all of the forum stuff and
really need an expert opinion on where I may be going wrong with m
conditional formatting - please see if you can spot my (obvious?
mistake
I thought that this would be the best place to come for som
good advice as I am now running out of ideas!!
Basically, I have been tasked to maintain a spreadsheet that holds al
of the dates of vaccinations and security clearances etc. My colleagu
has already created the s/s he would like to use, containing th
relevant information (unfortunately I cannot post it here so will hav
to try and explain). He does not want any additional rows or columns o
information containing values, he just wants conditional formattin
applied to the date cells to flag up when a date has passed a year, i
less than 6 months and is between 6 mths to a year. He wants th
formatting applied to future dates and dates retrospectively.
If I am looking at the dates retrospectively he would like any cell
that are greater than 360 days to go red, any cells that are less tha
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 b
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<36
goes amber (I have also tried this using >181 and <359 and have als
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 whic
way round I do this it will not format the cells to go amber!! As
said, red and green are absolutely fine and as soon as the days tic
over on the cells that should be amber i.e >360 or <180 they go green o
red??!!
If I add an extra column that gives me the value of TODAY()-A3 and appl
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
could just do the above it would be fine but my colleague is adamant w
just apply all arguments to the date cell. I can see where he is comin
from as it is so much tidier and easy to look at - I just can't for th
life of me figure where I am going wrong I'm assuming there is somethin
conflicting in the formula I am using?
I also need to apply this same criteria to dates in the future - i.
flagging up if it is less that 6 months to go red, more than a year t
go green and anything in between to go amber!!
I really hope this makes sense and I apologise in advance if I am bein
a bit of a miffy..............just don't have a clue where to go fro
here.
Many thanks in advance for all of your help
Lil P xx
Please bear with me as I am totally new to all of the forum stuff and
really need an expert opinion on where I may be going wrong with m
conditional formatting - please see if you can spot my (obvious?
mistake
I thought that this would be the best place to come for somgood advice as I am now running out of ideas!!
Basically, I have been tasked to maintain a spreadsheet that holds al
of the dates of vaccinations and security clearances etc. My colleagu
has already created the s/s he would like to use, containing th
relevant information (unfortunately I cannot post it here so will hav
to try and explain). He does not want any additional rows or columns o
information containing values, he just wants conditional formattin
applied to the date cells to flag up when a date has passed a year, i
less than 6 months and is between 6 mths to a year. He wants th
formatting applied to future dates and dates retrospectively.
If I am looking at the dates retrospectively he would like any cell
that are greater than 360 days to go red, any cells that are less tha
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 b
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<36
goes amber (I have also tried this using >181 and <359 and have als
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 whic
way round I do this it will not format the cells to go amber!! As
said, red and green are absolutely fine and as soon as the days tic
over on the cells that should be amber i.e >360 or <180 they go green o
red??!!
If I add an extra column that gives me the value of TODAY()-A3 and appl
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
could just do the above it would be fine but my colleague is adamant w
just apply all arguments to the date cell. I can see where he is comin
from as it is so much tidier and easy to look at - I just can't for th
life of me figure where I am going wrong I'm assuming there is somethin
conflicting in the formula I am using?
I also need to apply this same criteria to dates in the future - i.
flagging up if it is less that 6 months to go red, more than a year t
go green and anything in between to go amber!!
I really hope this makes sense and I apologise in advance if I am bein
a bit of a miffy..............just don't have a clue where to go fro
here.
Many thanks in advance for all of your help
Lil P xx