Hi Excellers, time for some more #formulafriday fun. Again this week this blog post is based on a question I was asked by a subscriber a few weeks ago and I thought it would be a good share it on #formulafriday.
They wanted to know how to easily calculate someone’s age using their data of birth in Excel. There are a couple of ways to calculate a person’s age from their date of birth. Let’s discuss how to do it using straight forward mathematics and then a method using the DATEDIF formula.
First let’s take a look at how Excel stores date and time, which is crucial to understand date and time calculations.
How Excel Stores Dates
First a quick explanation of how Excel stores dates, which helps a lot when trying to calculate dates and times. Excel stores dates or times as numbers representing the number of days passes since 1900 – Jan-00, plus a fractional proportion of a 24 hour day. (Known as serial date or serial date number).
So 1900-Jan-01 is day 1, 1900-Jan-02 is day 2 and so on as Excel adds one more day to the sequence, so by the time we get to today’s date ( 17 March 2017), the numbers are fairly big). Today’s date is represented by the number 42811.
How Excel Stores Time
So, Excel actually stores time in the same serial number formatting as the dates. The days begin at midnight (00:00) and as each hour is a 1/24 of a day then it is represented by 0.041666. So, 17 march 2017 at 9 pm will be stored as 42811.375.
Minutes are calculated in pretty much the same way, one minute is 1/60 of an hour, which is 1/24 of a day, or 1/1440 of a day in total, which calculates to 0.00069444.
A second is 1/60 of an minute, which is 1/60 of an hour, which is 1/24 of a day, or 1/86400 of a day in total, which calculates to 0.00001157407.
How To Calculate Age From A Date Of Birth In Excel Using Mathematics
So now we know how Excel deals with dates and times, let’s calculate some employee ages using math. We know the employees date of birth, and today’s date, so all we need to do is take on from the other – this gives us the number of days between the two dates. All we need to do then is convert this to year by diving the number of days by 365.25 and take the result as the number of years. See some examples below where we have used the TODAY() function to get today’s date.
How To Calculate Age From A Date Of Birth In Excel Using The DATEDIF Function.
The DATEDIF Function is perfect for calculating a persons age in Excel. Excel does not provide any help or documentation on this since Excel 2000, but it is still available- Only if you type the full formula in yourself.
The syntax of the DATEDIF formula is
=DATEDIF(Start_Date,End_Date,Interval).
- The Start_Date is the starting period that you want Excel to calculate. (This can be entered as a number, formula or string).
- The End_Date is the end or last date of the period that you want Excel to calculate. (This can also be entered as a number, formula or string).
- The Interval is how you want Excel to display the difference between the two dates and there are 6 different types of units available.
So, using our same data set but using the DATEDIF function. Using the Start_Date as DOB and End_Date as TODAY() we then instruct Excel to specify the interval as years (Y).
We get the same result but just with different methods.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. If you want to see all of the blog posts in the Macro Mondays Series you can do so by clicking on the link below.
