I have a spread sheet that shows dates when people have been assessed to be fit to carry out exercise I want to be able to put dates into a cell that are older than today e.g. 07.07.2013 and it turns the cell green then 11 months from that date the cell turns yellow and 12 months from date cell turns red. I can get conditional formatting to do this from the present date but not from older dates any help would be gratefully appreciated
07,. 07. 2013 = Green
07.06. 2014 =Yellow
07.07.2014 = Red
use three conditions and make sure you order RED 1st ,then Yellow ,then green
=cell with date < date(year(today()), month(today())-12, day(today()))
- thats red
=cell with date < date(year(today()), month(today())-11, day(today()))
- thats yellow
=cell with date <= today()
- thats green
for 2007 or 2010 excel version
Conditional Formatting
Highlight applicable range >>
cell with the date in
Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
one of the formulas from above
Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK
repeat for other formulas - order so its red,yellow,green and use stop if true
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
I tried the Formula's but got the following error message, I pasted the conditions you provided just to make sure they where correct. Thank you for your help and any further suggestions would be gratefully accepted.
You may not use unions, intersections, or array constants for conditional formatting criteria
do you want to attach a sample spreadsheet
see example attached
example excel pre exercise and risk assesment register for help.xlsx
Hi I have attempted to attach a file I will try again but not having much look. if I manage to get to you it explains what I am trying to doAttachment 313132example excel pre exercise and risk assesment register for help.xlsx
you need to apply to the range of cells E5:E14
and change the order to be red, yellow, green and use stop if true
also
in D
the conditional formatting is for all future dates
=AND($D5>EDATE(TODAY(),12))
D5 has to be greater than
the number of months after the start date
=EDATE(TODAY(),12)
is 21/4/15
and so D5 has to be greater than that, and so in the future
=AND($D5>EDATE(TODAY(),12)) = Red
=AND($D5>TODAY(),$D5>EDATE(TODAY(),11)) = yellow
=$D5>TODAY() = Green
All the formulas start from current date I would like to be able to insert any date and the formula to start counting from their. Do I have to use new formulas or can these be adapted for what I require. Can you have a look and Help thank you
Originally Posted by etaf
sorry not sure i understand - can you give a few examples of what dates you want to enter and what colour you expect to get
I will attach a example fileexample excel pre exercise and risk assesment register for help.xlsx
=AND($D5>EDATE(TODAY(),11)) = Red
=AND($D5>TODAY(),$D5>EDATE(TODAY(),10Attachment 313157)) = yellow
=$D5>TODAY() = Green
The dates will be from 2013 and 2014> on wards the cells to go to the colours above when reaching the set dates.
All the formulas start from current date I would like to be able to insert any date and the formula to start counting from their. Do I have to use new formulas or can these be adapted for what I require. Can you have a look and Help thank you
Attachment 313157
The file example excel pre exercise and risk assessment register20.xlsx (20.4 KB)is the file I am trying to make work Thank you for any help
really sorry not getting this
so for 2013 /2014
if the date is older than 1 year from today - turn red
=EDATE(TODAY(),-12)
will give a date of 21/4/13
now if you want the cell to be red use
D5<EDATE(TODAY(),-12)
which means the date in the cell is less then a year ago
for 2015/16
if you want it to also turn red if the date is greater than a year in the future
D5>EDATE(TODAY(),12)
for BOTH
if you want it to also turn red if the date is greater than a year in the future OR the date is older than a year ago
=OR(D5<EDATE(TODAY(),-12), D5>EDATE(TODAY(),12))
is that what you mean ?
I get error message can not attach file so thank you for your help, I will have to seek help elsewhere thank you again
OkI will have to seek help elsewhere thank you again
but if you want to continue here
how big is the file you are trying to upload
20.1kb I keep getting the message you do not have permission to do this refresh page and log in. thank you anyway.
you could use a internet sharing site for example
dropbox or Onedrive (skydrive) or other site
and post the link
OR if you want to I can give my email address via a PM here
the files have been added hopefully you can see them and help me thank you
can you perhaps give a range of both the dates for the colours
22/4/14 today
so what date ranges are RED, YELLOW GREEN
i'm signing off now for the night
the date ranges are
Green date to 10 months from start date
Yellow from 10 months to 11 months from start date
Red from 11 months to 12 months from start date
start date may can not be computer date, it may be a date before computer date but still has to start count from entered day. thanks for your help hope this is clear if not ask me next time you are on line.
so thats based on the start date entered in column D
cell with date to test > EDATE(D5,11)
so between 11 and 12 months in future
AND(cell with date to test > EDATE(D5,11), cell with date to test < EDATE(D5,12))
so if the startdate was
1/1/13 UK Format in D5
and you are testing the cell in E5
then it would be red if the date is
between 2/12/13 and 31/12/14
see a simple sheet here
change the dates in F5 and it will only be red if the date is between 2/12/13 and 31/12/13
is that close to what you want for red - if not what needs to change
if it is - we can then do yellow and green in a simple sheet and once that works
apply to your sample sheet uploaded and make sure it works across that sheet
if the start date in d5 = 1/1/13 when that cell reaches 10 months later I would like that cell to change to yellow and then 11 months later cell d5 turn red.
even if the date was any date eg 1/1/2014 green 1/10/2014/ yellow 1/11/2014 red
insert any date in the cell and the count starts and changes colour in that cell in that cell. I am not sure weather we can do it in conditional formatting.
All cells require this formula. again thank you for your help
Green: =now()-d5<303
Yellow: =NOW()-D5>303
Red: =NOW()-D5>333
Ben Van Johnson
the today() stops the formula from inputting any date and the start counting but I do not know what to use instead of the today argument
the formula works from the date inputted to the cell as long as it is the date on the computer I want to input days before the computer date and the count starts from that date I think the today() argument stops this
still having a problem if any one can help
cell = date = green
same cell = 10 months later = yellow
same cell = 11 months later = red
can not use today() value as the cell date maybe older than today()
still having a problem if any one can help
cell = date = green
same cell = 10 months later = yellow
same cell = 11 months later = red
can not use today() value as the cell date maybe older than today()
=cell with date < date(year(today()), month(today())-12, day(today()))
so that is testing to see if the date that is in the cell is less than a year ago compared to the date today()
so if the date is
1/Jan/2013 - then that is over 12 months when compared to the date today
=1/jan/2013 < date(year(today()), month(today())-12, day(today()))
today() is 25/4/14
and date(year(today()), month(today())-12, day(today()))
takes 12 months away from today()
so Today() - -12 months = 25/4/13
=cell with date < date(year(today()), month(today())-12, day(today()))
so
= 1/jan/13 < 25/apr/13
thats TRUE so = RED
if you could explain what you need in the same extended way - and what cells you want to compare
if you input a date into the system and want to start the count from that date - what do you mean - compare the input date with what other information
input date
1/jan/13
so when we are at 1/jan/14 it is a year old
but what are you using to test against , how will you know its the 1/jan/14 without using another cell or using the today() function to know the date when you open the spreadsheet ?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks