1. ## Calculating Age (Excel/2000)

I would like to know an easy way to calculate the difference between two dates and have the answer come up as years and months. The way I currently do it is through a series of formulas, entering the month and date in seperate cells and by way of using "if/then" statements in my formula get my answer. There has got to be easier way.

2. ## Re: Calculating Age (Excel/2000)

From <A target="_blank" HREF=http://www.cpearson.com/excel/datedif.htm#Age>Chip Pearson</A>, assuming there are cells containing valid dates called 'birthdate' and 'todate':

=DATEDIF(birthdate,todate,"y") & " years, " & DATEDIF(birthdate,todate,"ym") & " months, " & DATEDIF(birthdate,todate,"md") & " days"

Just trim off the last section if you don't want days. (I knew my own route wasn't getting there!)

3. ## Re: Calculating Age (Excel/2000)

What is that function, DATEDIF ?

I don't have that function available. And, I'm assuming that you're typing this formula into the Cell in a worksheet, right?

4. ## Re: Calculating Age (Excel/2000)

Chip Pearson's discussion of DATEDIF is so good I reproduce it here with acknowledgement; see the website link above.

<font color=blue> Chip Pearson, author:
"The DATEDIF function is a worksheet function that computes the difference between two dates. This function is available in all versions of Excel since version 5, but was never documented in the help files until Excel 2000. In Excel95 and earlier, DATEDIF resides in the Analysis ToolPak add-in module, so you must install this in order to use the function. In Excel97 and Excel2000, it is part of Excel proper, so you don't need to use the ATP.

DATEDIF has, for whatever reason, been treated as one of the drunk cousins of the Function Family. Excel knows he lives a happy and useful existence, and will acknowledge his existence when you ask, but will never mention him in "polite" conversation.

You can use DATEDIF as you would any normal worksheet function, because it is a normal worksheet function. Until Excel2000, it was never documented in the help files, but it has been around since at least Excel5. There has been, from time to time, an article in the Knowledge Base describing DATEDIF, but as often as not, the article is not available. Why? Who knows? One of the deep dark secrets known only the Softies."</font color=blue>

Does this help, considering the XL version you are using?

And, yes, entered directly in a cell, except that I specifically range-named two other cells 'birthdate' and 'todate'; you can use address references, and you can also substitute TODAY() for 'todate' if you always want to calculate age from birthdate through today.

5. ## Re: Calculating Age (Excel/2000)

John, can the DATEDIF Function be used to calculate age in years only with fractions rather than Years/Months/Weeksetc?

6. ## Re: Calculating Age (Excel/2000)

Using John's example, you could change it to <pre> =DATEDIF(birthdate,todate,"d")/365.25</pre>

That should work. Or if you have the Analysis Toolpak addin activated you could use <pre> =YEARFRAC(birthdate,todate,1)</pre>

Andrew C

7. ## Re: Calculating Age (Excel/2000)

Andrew has answered, but you could do this as simply as:

=(end_date - birthdate)/365.25

since Excel's time calculations use days as their base.

8. ## Re: Calculating Age (Excel/2000)

What if you would like to find the age on a specific date compared to there birthdate and not have it updated?

9. ## Re: Calculating Age (Excel/2000)

If you enter the specific (fixed) date in a cell and use that as todate or end_date in the various formulas proposed in this thread, and the birth date in another cell used as birthdate, the formula will always return the same result.
If you want to "freeze" the result, you can select the cell with the formula, copy it, then use Edit | Paste Special with the Values option to replace the formula with its result.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•