Template for tracking certificate expiry dates

**bandaid8** · 05-21-2009, 06:38 PM

Hi: I am trying to set up a template that tracks the courses taken by employee's across the top, the employee's names along the side, and the course (expiration) dates of each course in the worksheet. Each course has a different expiration period (most are 3 years, some are indefinite, some are two years). The template that I saw, the course date turned yellow if the expiration date was within 3 months, and red if it was within one month.

Anyone know how this is done?

All help will be appreciated.

**teylyn** · 05-21-2009, 08:02 PM

Hi,

this can be achieved with conditional formatting. Your first date, according to the setup described above should be in B2. Click that cell, then click Format - Conditional formatting.

Select Formula Is and enter the following

=DATE(YEAR(B2),MONTH(B2)-1,DAY(B2))<TODAY()

format the cell red for that condition.

then add another condition for three months away:

Formula is =DATE(YEAR(B2),MONTH(B2)-3,DAY(B2))<TODAY()
make that one yellow and hit OK

Now copy cell B2 and Paste Special - Formats to all other date cells you want to apply this test for.

hth

**bandaid8** · 05-21-2009, 08:09 PM

I will certainly try it, and let you know if it works. Thanks for your quick response!

**fahad.farooque** · 05-03-2012, 03:49 AM

Hey Can any one advice step by step how the above can be done in excel2007 step by step as i cannot find the formula IS

**arlu1201** · 05-03-2012, 05:12 AM

Fahad,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.