Table of Contents

Days until Birthday

To calculate the number of days until your birthday in Excel execute the following steps.

1. Enter the date of birth into cell B2.

Date of Birth

2. Enter the TODAY function into cell C2 to return today’s date.

Today

3. The most difficult part in order to get the number of days until your birthday is to find your next birthday. The formula below does the trick.

Next Birthday

Explanation: The DATE function accepts three arguments: year month and day. We used the DATEDIF function to find the number of complete years (“y”) between Date of Birth and Today. In this example DATEDIF(B2C2″y”) equals 42. If 42 complete years have passed since your date of birth (in other words you have already celebrated your 42nd birthday) your next birthday will be 42 + 1 = 43 years after your date of birth.

4. Next we use the DATEDIF function to find the number of days (“d”) between Today and Next Birthday.

Days until Birthday in Excel

Conclusion: Sharon’s birthday is in 32 days.

If you have a list of names and birthdates you can use Excel to highlight upcoming birthdays. This is pretty cool.

5. For example the formula below calculates how many days are left until Emily’s birthday.

Days until Birthday Formula

Note: this formula isn’t new. To create this formula take the formula from step 4 and replace D2 with the formula from step 3. Next replace C2 with $F$1.

6. Double click the lower right corner of cell C2 to quickly copy the formula to the other cells.

Upcoming Birthdays

Explanation: the absolute reference ($F$1) stays the same while the relative reference (B2) changes to B3 B4 etc. Visit our page about absolute reference to learn more about this topic.

7. To highlight upcoming birthdays within a week (green) or within a day (red) you can use conditional formatting in Excel.

Birthdays within 7 Days

8. This is what the spreadsheet will look like tomorrow.

John's Birthday is Tomorrow

Tip: visit our page about conditional formatting to learn more about this topic. You can download the Excel file (right side of this page) to see the applied conditional formatting rules.

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog