# How to calculate the days until the next birthday in Excel

Build this year's birthday, move it to next year if it has passed, and subtract today: `=DATE(YEAR($F$1)+(TEXT(B2,"mmdd")<TEXT($F$1,"mmdd")),MONTH(B2),DAY(B2))-$F$1`.

**Excel:** `=DATE(YEAR($F$1)+(TEXT(B2,"mmdd")<TEXT($F$1,"mmdd")),MONTH(B2),DAY(B2))-$F$1` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Name | Birthday | Days until |  | Today | 2026-10-29 |
| Grace | 1990-11-03 |  |  |  |  |
| Omar | 1984-10-29 |  |  |  |  |
| Lena | 2001-01-15 |  |  |  |  |
| Raj | 1978-10-12 |  |  |  |  |
| Ivy | 1996-02-29 |  |  |  |  |

Formula in C2, filled to C6: `=DATE(YEAR($F$1)+(TEXT(B2,"mmdd")<TEXT($F$1,"mmdd")),MONTH(B2),DAY(B2))-$F$1`

Result: 5; 0; 78; 348; 123

## How it works

`TEXT(…,"mmdd")` turns both dates into month-day codes like 1103 and 1029, so comparing them tells you whether this year's birthday has passed. If it has, the comparison is TRUE, which adds 1 to the year. Subtracting today gives the days to go: 0 for Omar, whose birthday is today.

## Other ways

- `=EDATE(B2,12*(DATEDIF(B2,$F$1-1,"y")+1))-$F$1`: Steps the birth date forward by whole years with EDATE; a February 29 birthday falls on February 28 in common years. (Ivy's birthday counts as February 28, one day earlier.)

## Common mistakes

- With DATE, a February 29 birthday becomes March 1 in common years. If yours should be February 28, use the EDATE version.
- `DATE(YEAR($F$1),MONTH(B2),DAY(B2))-$F$1` on its own goes negative once the birthday has passed.

## Questions

**How do I show the age they will turn?**

`=DATEDIF(B2,$F$1-1,"y")+1`: 42 for Omar today, 36 for Grace on November 3.

**How do I list the birthdays in the next 30 days?**

`=FILTER(A2:A6,C2:C6<=30)`, using the days-until column.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/days-until-next-birthday