Can I Spreadsheet?
← All how-to recipes

How to calculate the next birthday (or anniversary) date

✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)

From a birthdate, find when the next birthday falls — reminders, HR sheets, countdowns.

The formula

AppFormulaNotes
Excel (desktop)=EDATE(A2,12*(DATEDIF(A2,TODAY(),"y")+1))A2 = birthdate. Whole years lived so far, plus one, in months — EDATE lands on the next anniversary.
Google Sheets=EDATE(A2,12*(DATEDIF(A2,TODAY(),"y")+1))Identical.
LibreOffice Calc=EDATE(A2,12*(DATEDIF(A2,TODAY(),"y")+1))Identical.

How it works

DATEDIF counts completed years (34 for a Feb 1992 birthdate as of July 2026); adding one and converting to months lets EDATE jump from the birthdate straight to the NEXT anniversary — February 10, 2027. Because DATEDIF increments exactly on the anniversary, the formula rolls over correctly on the day itself. Days until it: subtract TODAY(). Leap-day birthdays (Feb 29) land on Feb 28 in common years via EDATE's clamping.

Verified, not just documented

We ran =TEXT(EDATE(DATE(1992,2,10),12*(DATEDIF(DATE(1992,2,10),DATE(2026,7,26),"y")+1)),"YYYY-MM-DD") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 2027-02-10 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 2027-02-10 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.

Functions used

EDATE · DATEDIF · TODAY — see full Excel, Google Sheets & LibreOffice compatibility for each.

Related recipes

Related comparisons