Birthday and Anniversary Reminder in Excel: Age, Next Birthday and Days Left (DATEDIF)

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: birthday-anniversary-reminder-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

As of 17-10-2026 (made-up dates):

Amir and Ravina's anniversary (12 October) has already passed this year, so the next one is in 2027. Three dates fall in the next 30 days. Ruhi is 11 today (DATEDIF) and turns 12 next week.

B3 holds the as-of date. In the template it is typed, so the numbers on this page match. Type =TODAY() in B3 to make the sheet live.

2. Build it step by step

Step 1. As-of date and list

1. In B3 type =TODAY() (the template has 17-10-2026 typed, so the example matches) (TODAY).
2. In A5:I5 type Name, Relation, Occasion, Date, Next date, Days left, Turning / years, Age now, Alert.
3. Type each person with the original date and year in D6 down.
4. Select B6:B200 > Data > Data Validation > List: Relative,Friend,Work, and C6:C200: Birthday,Anniversary (drop-down lists).

Step 2. Next date

1. In E6 type =DATE(YEAR($B$3)+(DATE(YEAR($B$3),MONTH(D6),DAY(D6))<$B$3),MONTH(D6),DAY(D6)).
2. Read it from the inside: DATE(YEAR($B$3),MONTH(D6),DAY(D6)) is the date this year. If it is before today, the comparison is TRUE, which counts as 1, so the year becomes next year.
3. Copy E6 down.

Step 3. Days left, turning and age

1. In F6 type =E6-$B$3: days left.
2. In G6 type =YEAR(E6)-YEAR(D6): the age they turn, or the years of marriage.
3. In H6 type =DATEDIF(D6,$B$3,"y"): full years completed today (DATEDIF).

Step 4. Alert

1. In I6 type =IF(F6=0,"Today!",IF(F6<=7,"This week",IF(F6<=30,"This month",""))) and copy down (nested IF).

Step 5. Summary

1. In L6 type =INDEX(A6:A13,MATCH(MIN(F6:F13),F6:F13,0)): Shahrukh comes up first.
2. In L7 type =COUNTIF(F6:F13,"<=30"): 3 in the next 30 days.
3. In L8 type =COUNTIFS(C6:C13,"Anniversary",E6:E13,"<="&DATE(YEAR(B3),12,31)): 1 anniversary left this year.

Step 6. Colours and sorting

1. Select A6:I200 > Conditional Formatting > New Rule > Use a formula: =AND($D6<>"",$F6=0) green and bold; then =AND($D6<>"",$F6<=7) amber (formula rules).
2. To see the order, select the list and Data > Sort by Days left, smallest to largest.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. Using the birth date directly for days left. =D6-$B$3 gives a big minus number. You need the next occurrence (column E) first.
2. Forgetting the year roll-over. Without the (…<$B$3) part, a birthday that has passed shows a minus number of days instead of next year's date.
3. Typing dates as text. If 24-10-2014 sits on the left of the cell, Excel sees text and DATE gives #VALUE!. Retype it as a real date.
4. 29 February dates. DATE turns 29 February into 1 March in non-leap years. Type 28 February or 1 March instead.
5. Typing years into Turning by hand. Let =YEAR(E6)-YEAR(D6) do it, so it is correct every year.

5. More real examples

Example 1: Work team list. Use Relation = Work and filter on it to see only the team's birthdays this month.

Example 2: Insurance and document renewals. The same next-date formula works for any yearly date: vehicle insurance, a yearly health check-up or a passport reminder.

Example 3: Month-wise list. Add =TEXT(D6,"mmm") and filter by month for a printable list for the fridge.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate the next birthday in Excel?

=DATE(YEAR(TODAY())+(DATE(YEAR(TODAY()),MONTH(D6),DAY(D6))<TODAY()),MONTH(D6),DAY(D6)) gives this year's date, or next year's if it has passed.

How do I calculate days until a birthday?

Next birthday − TODAY(). In the template =E6-$B$3.

How do I calculate age in Excel?

=DATEDIF(D6,TODAY(),"y") gives full years completed.

Why is DATEDIF not in the function list?

It is an old function Excel keeps for compatibility, so it does not appear in the suggestions. Type it in full; it still works.

Can Excel send me a birthday reminder?

Excel itself does not send alerts. Open the file each week, or copy the dates into your phone calendar as yearly events.

7. Quick quiz

Ten questions on this page. Sign in to save your score.