Is there a way to make this easier? I'll be doing it for the next twenty years or so...

December 29, 2011 9:47 AM

How can I automate email or text notifications to myself that any given employee's certification is a month away from expiring?

Each of my employees has between 6-10 various safety tickets and professional trades certifications which all expire on different dates that can be as far as three years in the future. Tracking all of these expirations is messy and I'd like to be able to use a program or online service or Google Docs spreadsheet function to somehow automate the process, but I'm not sure what to use or how best to do it.

What I need is a month's notice so that I can enroll the employee in the course again so that there's no lapse in certification. A plain spreadsheet that has to be constantly checked and reviewed is a major pain, and I figure there has to be some existing technology or something that can be re-purposed for my requirements.

I'd like to group the info by employee rather than type of certification, as there is unfortunately a high turnover rate in this field and I'll often be entering and deleting employees. I'd need to be able to update the expiration date once the employee completes the course again. It would be nice if the overall data was printable to provide to our various safety and QA auditors but it's not absolutely necessary as long as I can show that I'm tracking it (viewing it on my laptop would be fine).

And most importantly, I would like for a heads-up email or text to be automatically generated and sent to me, calculated as one month prior to any given entered expiration date (without me having to input the notification date in addition to all of the expiration dates). It's crucial that the notifications be dependable and reliable up to three years from any given date.

Is this possible, and if so, how?

posted by mireille to Computers & Internet (15 answers total)

Create a Google calendar for it. Set the reminder email for a month before the event. Print out the next 12 months of monthly views for your auditor.
posted by bfranklin at 9:52 AM on December 29, 2011 [2 favorites]

Do you use Outlook? I'd calendar each of these as tickles in your Outlook calendar, set a month or so before the relevant expiration date. You can set up automatic alerts, which will show up days in advance of the tickle date, if you'd like.
posted by bearwife at 9:53 AM on December 29, 2011

What email client or service do you have? Outlook can easily do it for you, or gmail with a google calendar will also do what you need.
posted by pazazygeek at 9:54 AM on December 29, 2011

Response by poster: The problem I have with just setting it as a calendar item is that I can't see all the info at-a-glance on each employee. I also can't just delete an employee and have all their certifications/reminders removed from the system in one go. And I do need to be able to show an auditor that I've got all of the information in one place so that they can look at a list of current employees and see that they're all current with their required certs.

To answer the questions about email clients, though, I use Thunderbird and Gmail. I've had a problem with Gmail calendar reminders - I had one set up for a daily (weekday) reminder and I'd say it generated about two per week over the course of a couple of months, which concerns me. I researched the issue and found that others had the same problem but no resolution. I have to be confident that the notifications are reliable, too, or the whole thing won't work.
posted by mireille at 10:00 AM on December 29, 2011

Response by poster: One clarification-- I need to be able to have the information organized per each employee, with (to use a spreadsheet format as an example) each type of certification named in one field, with "date acquired" and "date expiring" in the next two fields on the same line, with the automatic reminder (or automatic calendar reminder entry) being generated from the "date expiring" field-- and with multiple certification types listed under each employee.
posted by mireille at 10:06 AM on December 29, 2011

Are you on windows, osx, other unix variant? Are your spreadsheets all in the cloud, or do you have a local spreadsheet app?
posted by bfranklin at 10:13 AM on December 29, 2011

How big of a company do you work for? Certification tracking is a feature of many HR software packages. If your company has some sort of HRIS in place you may have the functionality already available. Of course, that doesn't mean it will actually be an improvement over a spreadsheet and/or Google Calendar.
posted by COD at 10:15 AM on December 29, 2011

Response by poster: Windows, use both Google Docs and Excel. Haven't put info into digital form yet, though, as I didn't want to have to duplicate efforts if I could find the ideal solution.
posted by mireille at 10:15 AM on December 29, 2011

Response by poster: COD-- My husband and I own the business (so I am HR, as well as everything else)-- and we're not quite big enough to use HR software just yet-- that would be ideal but the expense isn't currently justifiable.
posted by mireille at 10:18 AM on December 29, 2011

Do you have a database program? You could probably cook up your own database to cover this pretty easily. Databases would keep lists of employees, lists of relevant certifications, and their relationships plus expiry dates, and I am pretty sure you could just have it set up automatic emails.
posted by jeather at 11:01 AM on December 29, 2011

Checkout followup.cc, I'm doing a trial of it right now for similar purposes.
posted by effigy at 11:36 AM on December 29, 2011

Oops, I just read some of your responses and further requirements, so I'm not sure followup.cc would meet your needs.
posted by effigy at 11:37 AM on December 29, 2011

Okay, I've been wanting to play with powershell, so here's a possible solution:

- If you're running windows 7, you're gravy. If not, I need you to install Powershell 1 for XP in either the appropriate 32 bit or 64 bit flavor.
- Run Start->All Programs->Accessories->Windows PowerShell->Windows Powershell
- Enter the following at the prompt: Set-ExecutionPolicy RemoteSigned
- Copy the following block into a text file and save it as "report.ps1". Be sure to edit the variables at the top that are in all caps. Since your password will be in this file, I'd suggest setting up a gmail account specifically for this task.

$DestinationEmail = "YOUR EMAIL HERE";
$EmailFrom = "YOUR FROM ADDRESS HERE";
$EmailSubject = "Expiring/Expired Certification Update";
$smtpServer = "smtp.gmail.com";
$username = "YOUR USER HERE. DO NOT INCLUDE @GMAIL.COM";
$password = "PASSWORD";
$ExcelLocation = "C:\PATH\TO\YOUR\EXCEL\FILE";

$Excel = new-object -comobject excel.application;

# Replace with location of your excel file

$Workbook = $Excel.Workbooks.Open($ExcelLocation);
$Worksheet = $Workbook.Worksheets.Item(1)
$RangeRows = $Worksheet.UsedRange.Rows.Count;
$RangeColumns = $Worksheet.UsedRange.Columns.Count;

$CurrentDate = Get-Date;
$NotifyDate = $CurrentDate.AddMonths(1);

$Output = "";

for ($k = 2; $k -le $RangeRows; $k++)
{
	for ($j = 2; $j -le $RangeColumns; $j++)
	{  
        [datetime] $Date = new-object DateTime;
		if ([datetime]::TryParse($Worksheet.Cells.Item($k,$j).Text, [ref]$Date))
        {
            if ($Date -gt $NotifyDate)
            {
                $Output += $Worksheet.Cells.Item($k,1).Text + " has a " + $Worksheet.Cells.Item(1,$j).Text + " certification that expires on " + $Worksheet.Cells.Item($k,$j).Text + ".`n";
            }
            if ($Date -lt $CurrentDate)
            {
                $Output += $Worksheet.Cells.Item($k,1).Text + " has a " + $Worksheet.Cells.Item(1,$j).Text + " certification that expired on " + $Worksheet.Cells.Item($k,$j).Text + ".`n";
            }
        }
        else
        {
            $Output += "Failed to parse the date for " + $Worksheet.Cells.Item($k,1).Text + "'s " + $Worksheet.Cells.Item(1, $j).Text + " certification.  Please check this value.`n";
        }
	}
}

$SMTPClient = new-object Net.Mail.SmtpClient($smtpServer, 587);
$SMTPClient.EnableSsl = $true;
$SMTPClient.Credentials = new-object System.Net.NetworkCredential($username, $password);
$SMTPClient.Send($EmailFrom, $DestinationEmail, $EmailSubject, $Output);    

- Finally, follow these instructions to schedule the powershell script to run as a scheduled task. This script will email you every time it is run for certifications that are within the "danger zone" for expiration, so I'd suggest scheduling it to run weekly when you aren't in the office. This way you get nagged about approaching deadlines, but not annoyingly so.

One more note, the script expects a) that the data will be on the first worksheet in the spreadsheet, and b) that the first row contains column headings, beginning with the employee name. If you need help tweaking it for, e.g., working with a provider other than gmail, let me know.
posted by bfranklin at 12:15 PM on December 29, 2011

You can do it with Outlook. Make each employee a contact in your contacts. Almost nobody uses this feature, but it works. You need to find the option "include contacts on all forms" and turn it on.

Now, you make a task in your tasks list for the expiration date of each cert. Set the reminder to one month before. Set the contacts field of the task to the employee. Optional-recommended, set the category to the name of the individual cert. so you can filter on that when you need to.

Now in your contacts, open your employee, then click Activity. It will show all the tasks with that person as a contact.
posted by ctmf at 1:47 PM on December 29, 2011

Response by poster: bfranklin-- I am going to play with that over the weekend and report back-- it looks like a really interesting solution.
posted by mireille at 4:59 PM on December 29, 2011