How to Track Employee License and Certification Expirations in Google Sheets (with Email Reminders, 2026)

Professional licenses, first-aid and CPR cards, forklift and food-handler certificates, driver's licenses, permits: most small teams track them in a spreadsheet, and most find out one has lapsed when someone asks for it. A sheet with one row per credential, a few formulas and a daily reminder fixes that without buying an HR system.

This differs from a certificate of insurance tracker in two ways: the person who has to act is usually the employee (with a manager in the loop), and many renewals take weeks of lead time for courses, exams or paperwork, so different credentials need different warning periods.

Step 1: one row per person per credential

Create a tab named Credentials with these headers in row 1 (the script below finds columns by name, so the order doesn't matter):

HeaderWhat goes in it
EmployeeName of the person who holds it
Credentiale.g. “CPR/First Aid”, “Forklift operator”, “State RN license”
NumberLicense or certificate number (optional)
ExpiresA real date (Format › Number › Date)
EmailThe employee's email
Manager emailCopied on reminders (optional)
Remind daysComma-separated days before expiry, e.g. 90,30,7,0. Blank uses the default.
StatusLeave blank while active; Renewed, Inactive or Done stops reminders
File linkLink to the scanned card or certificate in Drive (optional)
Sent logLeave empty; the script records what it sent

Someone with three certificates gets three rows. That feels repetitive, but it's what lets every credential have its own date and its own lead time. Set lead times by how long renewal really takes: a card renewed with a two-hour class may only need 30 and 7 days, while a license that needs continuing-education hours or a board application may need 90 or more.

Step 2: formulas that show what's coming

Add a Days left column (here the expiry date is in column D, starting in row 2):

=IF(D2="","",D2-TODAY())

and an Expiry status column:

=IF(D2="","Missing date",IF(D2<TODAY(),"Expired",IF(D2-TODAY()<=30,"Expiring soon","Current")))

Use conditional formatting on the data range with custom formulas =AND($D2<>"",$D2<TODAY()) (red) and =AND($D2>=TODAY(),$D2-TODAY()<=30) (amber). For a quick “who is expiring next” view on another tab, sorted by date:

=QUERY(Credentials!A:J,"select A,B,D where D is not null and D <= date '"&TEXT(TODAY()+60,"yyyy-mm-dd")&"' order by D",1)

A training matrix. If managers prefer one row per person and one column per credential, build it with a pivot table (Insert › Pivot table) with Employee as rows, Credential as columns and MIN of Expires as values. Keep entering data in the one-row-per-credential tab; the matrix is just a view.

Step 3: email the employee (and copy the manager) before it lapses

Google Sheets doesn't send email when a date arrives on its own; its notification settings react to edits, not to the calendar. A short Apps Script with a daily trigger does it. Open Extensions › Apps Script, paste this, save, run sendCredentialReminders once to approve permissions, then add a trigger: Triggers › Add trigger › sendCredentialReminders › Time-driven › Day timer.

/**
 * Daily license / certification expiry reminders from a "Credentials" tab.
 * Headers used (any order): Employee, Credential, Expires, Email,
 * Manager email (optional), Remind days (optional, e.g. "90,30,7,0"),
 * Status (optional), File link (optional), Sent log (required, leave empty).
 */
const SHEET_NAME   = 'Credentials';
const DEFAULT_DAYS = [60, 30, 7, 0];
const STOP_STATUS  = /^(renewed|inactive|done)$/i;

function sendCredentialReminders() {
  const sh   = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  const rows = sh.getDataRange().getValues();
  const head = rows[0].map(String);
  const col  = (name) => head.indexOf(name);
  const c = {
    emp: col('Employee'), cred: col('Credential'), exp: col('Expires'),
    email: col('Email'), mgr: col('Manager email'), days: col('Remind days'),
    status: col('Status'), link: col('File link'), log: col('Sent log'),
  };
  if (c.exp === -1 || c.email === -1 || c.log === -1) {
    throw new Error('Need Expires, Email and Sent log columns.');
  }
  const tz    = Session.getScriptTimeZone();
  const today = new Date(); today.setHours(0, 0, 0, 0);
  const get   = (row, i) => (i === -1 ? '' : row[i]);

  for (let r = 1; r < rows.length; r++) {
    const row = rows[r];
    const exp = row[c.exp];
    if (!(exp instanceof Date) || !row[c.email]) continue;
    if (STOP_STATUS.test(String(get(row, c.status)).trim())) continue;

    const expDay = new Date(exp); expDay.setHours(0, 0, 0, 0);
    const left   = Math.round((expDay - today) / 86400000);
    const expKey = Utilities.formatDate(exp, tz, 'yyyy-MM-dd');
    const sent   = String(row[c.log] || '').split(';').filter(String);
    const custom = String(get(row, c.days)).split(',')
      .map((s) => s.trim()).filter((s) => s !== '').map(Number)
      .filter((n) => !isNaN(n));
    const days = custom.length ? custom : DEFAULT_DAYS;

    // Milestones that are due and not yet sent for THIS expiry date.
    const due = days.filter((d) => left <= d && !sent.includes(d + '@' + expKey));
    if (!due.length) continue;
    if (MailApp.getRemainingDailyQuota() < 1) return;   // try again tomorrow

    const when = left < 0 ? 'expired ' + (-left) + ' day(s) ago'
               : left === 0 ? 'expires today'
               : 'expires in ' + left + ' day(s)';
    const who  = get(row, c.emp) || 'You';
    const what = get(row, c.cred) || 'A credential';
    let body = 'Hi ' + who + ',\n\n' + what + ' ' + when + ' (' +
      Utilities.formatDate(exp, tz, 'MMM d, yyyy') + ').\n' +
      'Please start the renewal and send the new certificate once you have it.';
    const link = get(row, c.link);
    if (link) body += '\n\nCurrent file: ' + link;

    const msg = { to: String(row[c.email]), subject: what + ' ' + when + ' (' + who + ')', body: body };
    const mgr = get(row, c.mgr);
    if (mgr) msg.cc = String(mgr);
    MailApp.sendEmail(msg);

    // One email per run, even if several milestones are due (e.g. first run).
    due.forEach((d) => sent.push(d + '@' + expKey));
    sh.getRange(r + 1, c.log + 1).setValue(sent.join(';'));
  }
}

Two details make this safe to leave running:

Tips

Want reminders without maintaining a script? SheetReminders (disclosure: we make it) is a Google Sheets add-on that watches a date column and emails the right person before it arrives: several reminders per date (like 90, 30 and 7 days), personalized messages with {{Column name}} and {{Days left}}, a digest mode with one email per person, skipping rows marked Done, and a sent log written back to the row. It has a preset for licenses and certifications. Sheet data stays inside your Google account and it uses narrow scopes. 14-day free trial, no credit card required. It is live on the Google Workspace Marketplace.
See SheetReminders
Short on time? Watch the 50-second version

Related Google Sheets guides

Instructions refer to Google Sheets, Google Docs, Google Forms, Google Drive and Apps Script as they worked on November 11, 2026. Not affiliated with Google or any other vendor named here. Google Sheets, Google Docs, Google Forms, Google Slides and Google Drive are trademarks of Google LLC. Features change, so verify in your own account. Questions or corrections: hello@loomwise.app.