Let’s say you are preparing a data where user’s age is one of the important things to capture.

The thing is, if you ask every user to enter his/her age, chances are they can give you an approx number.

But here’s the kicker: If you ask them their date of birth then you can easily calculate their accurate age from it.

In Excel, you can **calculate a person's age** using different methods, but if you ask me, I have found that the best way is to use DATEDIF Function.

In today’s post, I’d like to share with you a simple way to calculate age in Excel using the date of birth with DATEDIF.

Download this sample file from here to follow along.

…here we go.

## Quick Intro: DATEDIF

Before you calculate age you need to get bit deeper into DATEDIF.

DATEDIF is a mysterious function which is not listed in Excel’s functio. But you can enter it and use it once you know what’s it’s syntax. ns list

This function’s basic work is to calculate the difference between two dates returns the result in different ways. i.e. years, months, days or in years and month, month and days, years and days.

And this is the feature which makes it perfect to use in age calculation.

### Syntax

DATEDIF(Date1,Date2,Interval)

## Steps to Calculate Age in Excel using DATEDIF

Here you need to calculate age in a format in which you could show years, months and days separately.

For example, 26 Years, 8 months & 17 days. And for this, we need to combine DATEDIF three times.

...here are the steps.

### [Step: 1] Calculate Completed Years of a Person’s Age

First of all, you need to calculate to total completed years between a date of birth and today's date.

And, for this the formula will be:

=DATEDIF(Date-of-Birth,TODAY(),"y")

This will return completed years between two dates.

### [Step: 2] Calculate Completed Months of a Person’s Age after Completed Years

Next thing is to calculate completed months. It means if you age is 26 Years, 5 months and 19 days, we need to get 5 months in return.

Below formula can be used:

This will return the completed months between the date of birth and today's date after completed years.

### [Step: 3] Calculate Completed Days of a Person’s Age after Completed Years and Months

In the end, you need to calculate days which are left after completed years and months.

Here is the formula:

This will return the rest of the days between the date of birth and today's date after completed years and month.

### [Step: 4] Combine All Three Formulas to Get Actual Age

Now it's time to combine all these to create an accurate age.

Enter below formula in a cell in your worksheet and replace Date-of-Birth with your actual birth date.

="You age is "& DATEDIF(Date-of-Birth,TODAY(),"y") &" Year(s), "& DATEDIF(Date-of-Birth,TODAY(),"ym")& " Month(s) & "& DATEDIF(Date-of-Birth,TODAY(),"md")& " Day(s)."

That’s all.

## Sample File

## Conclusion

You might be wondering that there are other methods which we can use to calculate age.

Yes, we have but as I said this is one of the easiest methods to do that. You can calculate person’s age accurately.

I hope you found this formula tip useful.

Now tell me one this.

*Have you ever calculated age in Excel?*

Share your views with me in the comment section, I’d love to hear from you and please don’t forget to share this tips with your friends.

Related Tips

- Text to Date: When is comes to textual date formats there is no one particular thing. It depends on the person who is entering [...]
- End of the Month Date: The best way to calculate the last date of the month is by using EOMONTH Function. But apart from this [...]
- Get Day Name: Let’s say you have a date from that date you want to get the day name i.e. Sat, Sunday. This can be done with [...]
- Get Month from Date: When it comes to Excel we have different methods to get a month from a date and I’ve found that there are [...]
- Highlight Dates Between Two Dates: You have dates in column and you want to highlight all the dates which are between the [...]
- Sum Values Between Two Dates: And with SUMIFS you can add those two dates as criteria and it works like a range and sum all the [...]

About the Author

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

Great

When I use DateDif it is working fine in Excel Sheet. But if I use it in VBA it is not accepting. May I have a code for Excel VBA.

I wish to calculate and group my inventory aging, Grouping is like 0- 60 Days , 60 – 90 Days, 90-180 Days

every time i learn new thing from U………….. Thank u So much

Hi, I working in need in power pivot sheet how to do if condition formula for age, How can I do this calculate

Hello Shinwari,

That was really helpful..

Thanks..

Hello Puneet,

Is it possible to use this function referenced to TWO cells (i.e., date-of-birth and date-of-visit) to calculate age in certain point of time?

Thanks

Absolutely. You just need to mention that date. Learn more about DATEDIF from here ==> https://excelchamps.com/blog/datedif/

Thanks!

Nice post 🙂

Thanks Ratanak

its not working

i have replaced the DOB with the data of birth

Hello Sami Shinwari,

Please use sample file to try.

Thanks

Puneet Gogia