How to Get Birthday and Work Anniversary Reminders from Google Sheets (2026)

Plenty of teams keep birthdays and start dates in a spreadsheet, and the sheet only helps if someone remembers to open it the week before. This guide sets up a People tab whose “next birthday” and “next anniversary” columns move to the coming year on their own, a view of everything in the next 30 days, and a free Apps Script that emails each manager a short list a few days ahead, with the years of service worked out for anniversaries.

The tricky part compared with an expiration tracker like the license and certification tracker is that these dates repeat every year. The date typed into the sheet is in the past (1991, or the day someone joined), so the sheet has to calculate the next time it comes around.

Step 1: a People tab

Create a tab named People with these headers in row 1. The script finds columns by name, but the formulas below assume this order (A to J):

ColHeaderWhat goes in it
ANamee.g. “Dana Ruiz”
BTeamOptional, for filtering
CBirthdayA real date. If someone would rather not share their age, use any year (for example 2000); only the month and day are used.
DStart dateThe day they joined
ENext birthdayA formula (Step 2), formatted as a date
FNext anniversaryA formula (Step 2), formatted as a date
GYearsA formula: years of service at the next anniversary
HManager emailWho should get the reminder. Blank uses the fallback address in the script.
IStatusLeave blank for current staff; Left, Inactive or Opted out stops reminders
JSent logLeave empty; the script records what it sent

Format C through F as dates (Format › Number › Date). Dates typed as text, like “March 4”, won't calculate.

Step 2: next dates that roll over every year

In E2 (Next birthday), fill down. It takes this year's month and day, and if that has already passed, uses next year's:

=IF(C2="","",LET(y,YEAR(TODAY()),d,DATE(y,MONTH(C2),DAY(C2)),IF(d<TODAY(),DATE(y+1,MONTH(C2),DAY(C2)),d)))

In F2 (Next anniversary), the same idea on the start date:

=IF(D2="","",LET(y,YEAR(TODAY()),d,DATE(y,MONTH(D2),DAY(D2)),IF(d<TODAY(),DATE(y+1,MONTH(D2),DAY(D2)),d)))

In G2 (Years), years of service on that coming anniversary:

=IF(F2="","",YEAR(F2)-YEAR(D2))

Because TODAY() recalculates every day, E and F always show the upcoming date: the day after a birthday, that row jumps ahead a year with nobody touching it. People born on February 29 get March 1 in non-leap years with this formula (the script below uses February 28 instead; change either one so they match your team's preference).

A Year 0 anniversary means the start date is still in the future. You can hide those with conditional formatting or filter them out of the view below.

Step 3: a “coming up in the next 30 days” view

On a new tab, this lists every birthday and anniversary in the next 30 days, soonest first:

=SORT(FILTER({People!A2:A,People!E2:E,IF(People!E2:E="","","Birthday")},People!E2:E<>"",People!E2:E<=TODAY()+30),2,TRUE)
=SORT(FILTER({People!A2:A,People!F2:F,"Anniversary: "&People!G2:G&" yrs"},People!F2:F<>"",People!F2:F<=TODAY()+30,People!G2:G>0),2,TRUE)

Put the two formulas side by side (for example in A2 and E2) and add headers above them. A FILTER that finds nothing shows #N/A; wrap it in IFERROR(…,"Nothing in the next 30 days") if that bothers you. Format the date columns as dates.

Step 4: email each manager a few days ahead

Sheets can't send an email when a date arrives on its own; its notification rules react to edits, not the calendar. A short Apps Script on a daily trigger does it, and groups everything for the same manager into one email. Open Extensions › Apps Script, paste this, save, run sendBirthdayReminders once to approve permissions, then add a trigger: Triggers › Add trigger › sendBirthdayReminders › Time-driven › Day timer.

/**
 * Daily birthday and work anniversary reminders from a "People" tab.
 * Headers used (any order): Name, Sent log, and Birthday and/or Start date.
 * Optional: Manager email, Remind days (e.g. "7,0"), Status.
 * The script works out the next occurrence itself, so it doesn't
 * need the formula columns.
 */
const SHEET_NAME     = 'People';
const DEFAULT_DAYS   = [7, 0];      // a week ahead, and on the day
const FALLBACK_EMAIL = '';          // e.g. 'hr@yourcompany.com' for rows with no manager
const STOP_STATUS    = /^(left|inactive|opted out|paused)$/i;

function sendBirthdayReminders() {
  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 = {
    name: col('Name'), bday: col('Birthday'), start: col('Start date'),
    email: col('Manager email'), days: col('Remind days'),
    status: col('Status'), log: col('Sent log'),
  };
  if (c.name === -1 || c.log === -1 || (c.bday === -1 && c.start === -1)) {
    throw new Error('Need Name, Sent log, and Birthday or Start date columns.');
  }
  const tz    = Session.getScriptTimeZone();
  const today = new Date(); today.setHours(0, 0, 0, 0);
  const get   = (row, i) => (i === -1 ? '' : row[i]);
  const queue = {};                  // recipient -> { lines: [], logs: [[row, value]] }

  for (let r = 1; r < rows.length; r++) {
    const row = rows[r];
    if (!row[c.name] || STOP_STATUS.test(String(get(row, c.status)).trim())) continue;
    const to = String(get(row, c.email) || FALLBACK_EMAIL).trim();
    if (!to) continue;
    const custom = String(get(row, c.days)).split(',')
      .map((s) => parseInt(s, 10)).filter((n) => !isNaN(n));
    const days  = custom.length ? custom : DEFAULT_DAYS;
    const sent  = String(row[c.log] || '').split(';').filter(String);
    const lines = [];

    [['B', c.bday, 'birthday'], ['A', c.start, 'work anniversary']].forEach(([tag, i, label]) => {
      const src = get(row, i);
      if (!(src instanceof Date)) return;
      const next = nextOccurrence(src, today);
      const left = Math.round((next - today) / 86400000);
      // Smallest reminder day we've reached that hasn't been sent for this date.
      const hit = days.filter((n) => left <= n).sort((a, b) => a - b)[0];
      if (hit === undefined) return;
      const key = `${tag}${Utilities.formatDate(next, tz, 'yyyy-MM-dd')}@${hit}`;
      if (sent.includes(key)) return;
      const when = left === 0 ? 'today' : left === 1 ? 'tomorrow' : `in ${left} days`;
      let line = `${row[c.name]}: ${label} ${when} (${Utilities.formatDate(next, tz, 'EEE, MMM d')})`;
      if (tag === 'A') {
        const yrs = next.getFullYear() - src.getFullYear();
        if (yrs < 1) return;           // start date hasn't happened yet
        line += `, ${yrs} year${yrs === 1 ? '' : 's'}`;
      }
      lines.push(line);
      sent.push(key);
    });

    if (!lines.length) continue;
    const q = (queue[to] = queue[to] || { lines: [], logs: [] });
    q.lines.push(...lines);
    q.logs.push([r + 1, sent.slice(-12).join(';')]);
  }

  Object.keys(queue).forEach((to) => {
    if (MailApp.getRemainingDailyQuota() < 1) return;  // try again tomorrow
    const q = queue[to];
    MailApp.sendEmail(to,
      `Coming up: birthdays and anniversaries (${q.lines.length})`,
      `Heads up:\n\n- ${q.lines.join('\n- ')}\n\nSent from the People sheet.`);
    q.logs.forEach(([r, v]) => sh.getRange(r, c.log + 1).setValue(v));
  });
}

// This year's (or next year's) date with the same month and day.
// Feb 29 becomes Feb 28 in non-leap years.
function nextOccurrence(src, today) {
  const on = (y) => {
    const d = new Date(y, src.getMonth(), src.getDate());
    return d.getMonth() === src.getMonth() ? d : new Date(y, src.getMonth() + 1, 0);
  };
  const d = on(today.getFullYear());
  return d < today ? on(today.getFullYear() + 1) : d;
}

How it behaves:

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. Point it at the Next birthday or Next anniversary column from Step 2, which always holds the upcoming date: several reminders per date (like 7 and 0 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. 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.