Don't Wait for your Certificates to Expire

(For a Conditional Formatting Tutorial, scroll down.)

For those that study online or have to take courses for their company, it's bound to happen eventually. You put that certificate in your portfolio and two years later, you forget it's obsolete and you have an embarrassing time in front of a company recruiter. Maybe your employer just contacted you that you owe them a course. Maybe you are the employer who has to know exactly when the courses of your employees are about to expire. In any of those cases, here is a possible solution for you.

"Why didn't I get notified BEFORE the certificate expired?"

One easy method is to have an Excel spreadsheet that has all of your certificates and their expiration dates. Then check it every now and then. However, with a basic spreadsheet, you'll have to check the dates one by one and compare. If you are the employer and have over 100 employees with 20 certificates each? That'll get tedious and annoying. If you have a spreadsheet that is like that, here's a tip for you. Conditional Formatting.

"Conditional Formatting? Sounds complicated."

Conditional formatting is when a cell gets formatted due to a specific condition. The condition? Whatever you want, as long as you know how to write it correctly. The format can be a change in color, font, borders, type of cell, or have an icon appear to tell you something. The point is, it makes it extremely easy to view when a condition has been met.

"So how can it help with certificates expiring?"

Imagine the spreadsheet basically showing you which course is expired by a sharp red cell. Better yet, imagine the cell turning bright yellow to show you that it will expire in less than a month. Much better than having to go one by one. Now for the most important question.

"How do I do it? I want to simplify work."

First, let's go over an expired course and have it show us bright red when it's expired.

Once the names of the employees or yourself have been filled, along with the course names and expiration dates, simply select all of the expiration dates.

After you have them selected, go to the Home section on the Ribbon and look for Styles. There, you should see Conditional Formatting with an icon of some colored cells.

Once on the dropdown, you'll want to go to the last option that says Manage Rules... A new box will show and on this one, it'll say "Current Selection" on the top and under it, "New Rule..."

In the new box, you'll have yet another list of options under "Select a Rule Type:" There, you'll want to choose "Use a formula to determine which cells to format." You'll see some changes at the bottom where you will place your small code. Simply type without the quotation marks, "=B2<TODAY()". Where the "B2" is, belongs to the first cell you've selected in the group. Now click on "Format..."

Here's you'll want to verify the "Number" Tabe and ensure the category selected in "Date" and that the "Type" is the one you actually want. From there, you can change the font if you desire and add or remove borders. White lettering would help visibility of the dates in a red background. In this scenario, we'll go with "Fill". Select the color you want. We'll use red in this example. Now click on "Ok".

Everything should be in proper order. Hit "Ok" again and you'll see the rules manager again. It'll show you everything you've just set the rules to. Hit "Ok" one last time, and watch the expired dates turn red.

It's important to know which courses are expired, but that still leaves us with one problem. You know which ones are already expired, not which ones are about to expire.

"So now I know when a course is expired... How can I actually prevent that from happening instead of waiting til it's too late?"

The previous conditional formatting can stay the same for this part, though I will use different dates for this example.

As we can see, two courses are expired and it's easy to tell the other two are good, but in a table with hundreds of dates, you'd have a harder time identifying them. Let's add another rule to the conditional formatting manager to format those who are about to expire in less than a month. The code will be without quotation marks, "=B2-TODAY()<=30" and the format color will be yellow to help us identify it faster.

The result may not be what you expect...

This can be expected when you are working with multiple rules. If this happens, the rule that tells you which are about to expire first, and in this scenario, it would go backwards to give you the result you want. On the rules manager, there are arrows on the right side to move the rules around. Switch them around and watch the difference. From this...

To this...

The new result will be the expected outcome.

If you want to know which one is to expire even further on such as two months, just change the number from 30 to 60. This should be able to help you identify much easier which course will expire soon and which is expired, or anything else you need to know an expiry date. If you have any doubts or concerns, feel free to contact me.