Sponsored Link

There was an error in this gadget

Sponsored Link

Thursday, January 22, 2009

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

age1

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!

0 comments:

Post a Comment