Calculating a person’s age in Excel.

I have seen blogs and tutorials use the function YEAR() to calculate the difference between two dates and based on the answer, the number of years is calculated by dividing the answer with 365.25. This is one way of doing it but there is a better way.

To perform the above calculation there is a neat function called DATEDIF(), the surprising thing is, this function does not appear in Formula AutoComplete (You know, when you start typing a function, excel will immediately list all the functions starting with the alphabets you are typing, this is called Formula AutoComplete). This particular function does not appear in the list at all! (Is it an easter-egg?)

Ok let me show you how to use the function (Turn your pc/laptop on and follow the steps!)

Step 1: Key in the Date of Birth (DOB) in cell (B1).
Step 2: Key in today’s date in cell (B2) (When I was preparing this tutorial the date was 23 May 2013).
Step 3: Click in a new cell (B4) and key in the following formula =DATEDIF(B1, B2, “Y”) and press ENTER.

Excel will immediately calculate the number of years passed since the DOB till today!
Neat huh!

Pic 101

Ok let me explain. Look at the diagram below.

Pic 102

The “Start Date” is the date of birth, the “End Date” is the current date or todays date and the “Interval” is the value that you want to calculate. The interval can be Year (Y), Month (M) or Day(D).
Lets use the other intervals and extend the calculation to calculate the months and days.

Step 4: Click in another new cell (B5)and key in the following formula =DATEDIF(B1, B2, “M”) and press ENTER. Excel will immediately calculate the number of months passed since the DOB till today!
Step 5:  Click in again another new (B6)cell and key in the following formula =DATEDIF(B1, B2, “D”) and press ENTER.

Excel will immediately calculate the number of days passed since the DOB till today.

Pic 103

Wait not so fast! What is the point of telling someone that you are 544 months old since your birth. What we really want is a more meaningful calculation so that you can arrive to a more meaningful age. In the above case, a more meaningful age calculation will be 45 years 4 months and 13 days. Since the date of birth is 10 January 1968 and the current date is 23 May 2013 so it has been 45 years 4 months and 13 days that have passed since the DOB!

To get the above results, you need to change the formulas in B5 and B6 as follows:

In cell B5 -> =DATEDIF(B1, B2, “YM”)

In cell B6 -> =DATEDIF(B1, B2, “MD”)

Now we have a meaningful result!

Pic 104

The interval “YM” calculates the number of months between two dates as if the dates were in the same year.

The interval “MD” calculates the number of days between two dates as if the dates were in the same month and year.

You can also use “YD” which calculates the number of days between two dates as if the dates were in the same year.

TRY IT! USE IT! EXPLORE IT!

  WAIT!DO NOT PRINT THIS PAGE. If you print it will run into more than 4 pages! Get my E-pages, the whole tutorial is condensed into two A4 size pages with beautiful formatting for easy reading in pdf for free, no need to register.
Download E-page: Click here
E-Page

Posted on May 8, 2013 by Senthi .

Category:-

Leave a Reply