How to calculate the next birthday (or anniversary) date
✓ Verified in LibreOffice 25.8.7.3From a birthdate, find when the next birthday falls — reminders, HR sheets, countdowns.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =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. Every formula here is confirmed by actually executing it.