Home ➜ Excel Formulas ➜ Calculate Age using Date of Birth
Let’s say you are preparing data where the 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.
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.
Quick Intro: DATEDIF
Before you calculate age you need to get a bit deeper into DATEDIF. DATEDIF is a mysterious function that is not listed in Excel’s functions list. But you can enter it and use it once you know what’s its syntax.
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 months, month and days, years and days.
And this is the feature that makes it perfect to use in age calculation.
Syntax
DATEDIF(Date1,Date2,Interval)
- Date1 – The date from where you want to start the calculation of your interval.
- Date2 – The date to which you want to calculate the interval.
- Interval – It represents the unit of the time, which will return from the function.
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.
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.
2. Calculate Completed Months of a Person’s Age after Completed Years
Next thing is to calculate completed months. It means if your age is 26 Years, 5 months, and 19 days, we need to get 5 months in return.
Below formula can be used:
=DATEDIF(Date-of-Birth,TODAY(),”ym”)
This will return the completed months between the date of birth and today’s date after the completed years.
3. Calculate Completed Days of a Person’s Age after Completed Years and Months
In the end, you need to calculate the days that are left after completing years and months. Here is the formula:
=DATEDIF(Date-of-Birth,TODAY(),”md”)
This will return the rest of the days between the date of birth and today’s date after completing years and months.
4. Combine All Three Formulas to Get Actual Age
Now it’s time to combine all these to create an accurate age. Enter the formula below 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.
🔙 Excel Formulas \ 📂 sample-file
More on Date-Time Formulas
Add Minutes to Time | Add Month to a Date | Add Years to Date | Add-Subtract Week from a Date | Change Time Format | Compare Two Dates | Convert Date to Number | Create a Date Range | DATEDIF Function | Get Day Number of Year | Get Month from a Date | Get Quarter from a Date | Get Years of Service | Calculate Time Difference Between Two Times | Get Day Name from a Date | Get End of the Month Date | Get Total Days in Month | Highlight Dates Between Two Dates | Military Time (Get and Subtract)
Hi Boss!
I have a vba code to create a bulk salary slip in pdf with single click. Is it possible to add a password on the pdf file automatically with the specific cell as a password.
Thanks Narayanan
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