Hi I have a training sheet where the training could expire in either 1 2 or 3 years, i am trying to get the conditional formatting for each to change color when the training is due to expire in 6 months 3 months and 1 month also to show red when expired can someone please help...
hi otenemel,
CF formula, new rule > use formula
example cell A1 is your date, then the formula =
=(A1=EOMONTH(TODAY(),1)) <-1 month
=(A1=EOMONTH(TODAY(),3)) <-3 month
=(A1=EOMONTH(TODAY(),6)) <-6 month
-If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".
-Always upload a workbook before start your question
To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
+++ If my answer(s) helped you, please add me reputation by click on * +++
Hi there this isnt working???
what if the dates are like 1 year or 2year or 3 year due to expire
EOMONTH = Returns the serial number for the last day of the month that is the indicated number of months before or after start_date.
My mistake.
EDATE = Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date).
=(A1=EDATE(TODAY(),1)) <-1 month
=(A1=EDATE(TODAY(),3)) <-3 month
=(A1=EDATE(TODAY(),6)) <-6 month
=(A1=EDATE(TODAY(),12)) <-1 year
=(A1=EDATE(TODAY(),24)) <-2 year
=(A1=EDATE(TODAY(),36)) <-3 year
um what i mean is that some training needs to be done every 3 years some every 2 years some every year, but as there are different training sessions conducted on different dates, everybodys training will expire on different dates, what i want is that if the training is to be conducted every 3 years, the cell where the original date of training is to go orange when 3 months to expiry yellow one month to expiry and red when expired, im guessing this will be different again if the training is to be conducted every 2 years???
at the moment i have setup as cell value between =today()-182 and =today()-547 for certifictaion that is required every 2 years and is a year from expiring to go green, i am having trouble setting it up for monthly and each year like 1 2 or 3
Okay, i think i misunderstood your question.
Can you upload a sample workbook, will manually highlight, if meet the condition.
So i can more clear about this. Thanks
how do i upload?
Check the forum rule. http://www.excelforum.com/forum-rule...rum-rules.html
No.13, point 5.
Post a WORKBOOK.
To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
Please see attachment
As you will see there are some that are 3 yearly 2 yearly and yearly, but i would like each to show me when due to expire within 6 months, 3 months 1 month and expired
or even 3 months 1 month and expired
Hi otenemel, lets clarify my doubt.
So for cell J5 till R11.
Under 1 Year / 2 Year / 3 Year
those date is their training start day.
If that date under 1 year, it will expired after 1 year,
eg 01/Aug/12, will expired when 01/Aug/13.
So you need it be highlight when
the date left 6 months before expired -> Green
the date left 3 months before expired -> Orange
the date left 1 months before expired -> Yellow
the date is expired -> Red
Am i understanding correct?
yes that is correct
example (eDIT).xlsx
for 1 year
=edate(cell's name,12)<today() -> r
=edate(cell's name,11)<=today() -> y
=edate(cell's name,9)<=today() -> o
=edate(cell's name,6)<=today() -> g
for 2 year
=edate(cell's name,24)<today() -> r
=edate(cell's name,23)<=today() -> y
=edate(cell's name,21)<=today() -> o
=edate(cell's name,18)<=today() -> g
for 3 year
=edate(cell's name,36)<today() -> r
=edate(cell's name,35)<=today() -> y
=edate(cell's name,33)<=today() -> o
=edate(cell's name,30)<=today() -> g
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks