Birthday reminder Excel: build a sheet with formulas

To build a birthday reminder in Excel, save each person's month and day, calculate their next birthday, and highlight the ones coming up within seven days. The copyable layout below rolls past birthdays into next year without needing birth years. It gives you a visual reminder when you check the workbook; these formulas don't send phone notifications.

1. Set up the birthday reminder sheet

Enter these headings in row 1, from A1 through G1:

Fill in names, months, days and notes. Leave columns D–F empty for now.

Use numbers for the month: January is 1, December is 12. Keeping month and day separate avoids ambiguity such as whether 3/4 means March 4 or April 3. You don't need anyone's birth year for this countdown.

Outside the list, type Today in J1 and enter this in J2:

=TODAY()

Every row will reference this one date. Microsoft's TODAY documentation explains that it returns the current date and depends on workbook recalculation.

2. Add the birthday reminder formulas

Enter the following formulas in row 2. They use English function names and comma separators. Depending on your location settings, Excel may require semicolons instead, as explained in Microsoft's formula-error guide.

D2: construct this year's birthday

=IF(OR(B2="",C2=""),"",DATE(YEAR($J$2),B2,C2))

E2: find the next birthday, including today

=IF(D2="","",IF(D2<$J$2,DATE(YEAR($J$2)+1,B2,C2),D2))

F2: count the days remaining

=IF(E2="","",E2-$J$2)

The helper in D2 builds a date from the current year, month and day. E2 keeps that date unless it has already passed, in which case it constructs next year's date. F2 subtracts today from the result.

The strict < comparison matters: a birthday happening today stays in this year and shows 0 days left. The blank checks leave the calculated cells empty when a month or day is missing. Microsoft's IF documentation describes how those conditional branches work.

Select D2:F2 and copy the formulas down through your last person's row. The $J$2 reference stays fixed while each row's birthday references change.

Format D and E as dates and F as a number with zero decimal places. If a date appears as a large number, its display format needs changing; Microsoft explains this in its DATE function instructions.

3. Highlight birthdays coming up within seven days

For desktop Excel, select your populated rows across columns A–G, starting at row 2. For example, select A2:G20.

1. Open Home → Conditional Formatting → New Rule.
2. Choose Use a formula to determine which cells to format.
3. Enter this formula:

=AND(ISNUMBER($F2),$F2>=0,$F2<=7)

4. Choose a fill color and confirm the rule.

This highlights the entire row when the countdown is between 0 and 7, including both endpoints. ISNUMBER keeps empty calculated cells out of the highlighted group. Change 7 to 14 if you want two weeks' notice.

These controls follow Microsoft's conditional-formatting guide. The dollar sign fixes column F; the row number can change for each person.

To put the nearest birthdays first, select the whole populated list, including its headings, and use Data → Sort. Identify the header row and sort Days left from smallest to largest. Keep the names, dates and notes together, and reapply the sort after countdowns change. Microsoft's sorting instructions cover both points.

4. Check rollover and leap-day behavior

Before adding your full list, temporarily replace J2 with:

=DATE(2026,10,14)

The example entries should produce:

You can also test a year boundary: with J2 set to =DATE(2026,12,29), a January 2 birthday should be 4 days away.

February 29 needs a deliberate choice. These formulas produce March 1 in a non-leap year and February 29 in a leap year. That's a consequence of DATE carrying excess days into the following month, documented by Microsoft. Ask which day the person celebrates on if you need a reminder on February 28 instead.

Check other dates carefully too. DATE can turn an invalid entry such as April 31 into May 1 rather than rejecting it.

When you've finished testing, restore J2 to =TODAY(). If it later shows an old date, check recalculation. In Excel for Windows, Microsoft's TODAY page directs you to File → Options → Formulas → Calculation options → Automatic.

Use the same layout in Google Sheets

For a birthday reminder Google Sheet, keep the same headings, J2 reference and formulas. Google's function list documents the functions used here, including DATE, YEAR, IF, OR, AND and ISNUMBER.

Google also notes that TODAY reflects the date at the last recalculation. Check that cell when reviewing the list. The Excel menu steps above don't describe Google's formatting controls.

If you only need a printable list, the birthday list template offers a simpler layout without formula columns.

Pair the spreadsheet with iPhone reminders

Keep the sheet for your notes and use Birthday Countdown for alerts on your iPhone. It shows days remaining for saved birthdays, puts the next birthday at the top, and supports more than one reminder per person.

To add a birthday:

1. Open Birthdays and tap + at the top right.
2. On New Birthday, enter First name and select the day and month. Add an optional last name, note and groups.
3. Tap Reminders, then +. Choose the birthday itself, 1 day, 3 days or 1 week before, or a custom number of days. Set the time and tap Save Notification.
4. Tap Save Birthday.

Birthday Countdown is free for up to 5 active birthdays; unlimited birthdays require Premium. Its reminders are local iPhone notifications and need notification permission. You enter birthdays manually, and the app saves day and month without calculating ages.

For more about choosing reminder timing and checking limits, see the birthday reminder app checklist.