Conditional Formatting for expiry dates

[otenemel]

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...

[wenqq3]

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

[otenemel]

Hi there this isnt working???

[otenemel]

what if the dates are like 1 year or 2year or 3 year due to expire

[wenqq3]

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

[otenemel]

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???

[otenemel]

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

[wenqq3]

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

[otenemel]

how do i upload?

[wenqq3]

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.

[otenemel]

Please see attachment

[otenemel]

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

[otenemel]

or even 3 months 1 month and expired

[wenqq3]

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?

[otenemel]

yes that is correct

[wenqq3]

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