Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.
Worth $20, Absolutely Free
Let’s say you are preparing a data where user’s age is one of the important things to capture.
Now, 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.
This is a simple solution.
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.
And in today’s post, I’m going to show you how to calculate age in Excel using the date of birth with DATEDIF.
So let’s get started.
Before you calculate age, you need to get bit deeper in DATEDIF function.
DATEDIF is a mysterious function which is not listed in Excel’s functions list. But you can enter it and use it once you know what’s it’s 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 month, month and days, years and days.
And, this is the feature which makes it perfect to use in age calculation.
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.
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:
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:
This will return completed years between two dates.
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.
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.
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)."
Download this sample file from here to learn more.
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? Which method have you used? Please share 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.