How to calculate Exact Age

There are many alternate methods to calculate exact age in MS Excel. In this post, i will try my best to explore Datedif function and using Datedif to find age.

Datedif require three parameters like past date, current date, type of answer you need. Like whether you need answers in day, month, year, days excluding year, months excluding year and days excluding months. This may sound little confusing now. But do not worry. By the time, you will complete reading this post. I am sure you will learn one more function of MS Excel.

Earlier I have posted long formula. But, in the course of time I got this new idea which i wish to share with you.

Past Date	Current Date	Interval	Formula pattern	Results
11/24/1978	2/26/2009	D	=DATEDIF("11/24/1978","3/26/2009","D")	11080
11/24/1978	2/26/2009	Y	=DATEDIF("11/24/1978","3/26/2009","Y")	30
11/24/1978	2/26/2009	M	=DATEDIF("11/24/1978","3/26/2009","M")	364
11/24/1978	2/26/2009	YM	=DATEDIF("11/24/1978","3/26/2009","YM")	4
11/24/1978	2/26/2009	YD	=DATEDIF("11/24/1978","3/26/2009","YD")	61
11/24/1978	2/26/2009	MD	=DATEDIF("11/24/1978","3/26/2009","MD")	2

Here is description on interval parameter

D will return number of days between two days.

Y will return number of years between two days.

M will return numbers of months between two days.

YM will return number of months left after subtracting years or months if both dates have difference of less then one year.

YD will return number of days after subtracting years. Like 11/24/1978 and 2/26/2009 has difference of 30 years between 11/24/1978 to 11/24/2008 but there are 61 days between 11/24/2008to 2/26/2009.

MD will return days left after subtracting 11/24/1978 to 02/26/2009

Now lets us e this to calculate age in MS Excel. Suppose we have Date of birth store in A1 cell as shown in the image below. Then formula to calculate age will be as follows.

= DATEDIF(A1, TODAY(), "Y") & " yrs " & DATEDIF(A1,TODAY(),"YM")& " mnths " & DATEDIF(A1,TODAY(),"md") & " dys"

Click on image below to see example

This is very useful in calculating age of person, a ticket pending at customer service or anywhere where you want to track age.

You don't have to pay anything for leaving comments. So, please think about it. Also, you can receive free add-ins, e-books and update via email

We assure you knowledge, not SPAM!