Calculation of ageClosed

Question

Hello All,

I have been trying to calculate the exact age of a person from two dates in Office 2007. In the first cell I have the current date and in the second cell I have the Date Of Birth of the person. I need the age of the person in the third cell calculated as per the date, month and year provided. I have formatted the cell to Format Cells -> Number -> Custom -> Type: YY I have been trying the below formula but its not working.

=INT((current date-date of birth)/365.25)


Thanks in advance for your answer!

vcoolio · Answer

Hello Arzanm,

Perhaps using the DATEDIF function should do the task for you:-

=DATEDIF(A1,TODAY(),"y")&" years, "& DATEDIF(A1,TODAY(),"ym")  & "  month(s), "& DATEDIF(A1,TODAY(),"md") &" days"

The formula assumes that the start date (birth date) is in A1. The start date must go first. You can also use the actual start date instead of the cell reference. For example, if the date of birth (start date) is 12/11/2008, replace the cell reference with this date but enclose it as such: "12/11/2008" in the formula. TODAY() is, of course, the TODAY() Function and you won't need to make an actual reference to the current date.

FYI:-
"ym" - returns the difference between the months in Start Date and End Date (Today()). Here the days and years of the dates are ignored.

"md" - returns the difference between the days in Start Date and End Date (Today()). Here the months and years of the dates are ignored.

I hope that this helps.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Arzanm,

I have just tested the formula using your cell references and it works fine. A1 is irrelevant as you say in your first post "current date" which is today's date.

Did you change the cell references in the formula to suit yourself? That is, did you change A1 in the formula to C1?
Did you place the formula in D1?

When you say "Age", are you wanting just the years or do you need the months and days also which is what the formula gives?

Cheerio,
vcoolio.

vcoolio · Answer

Hello Arzanm,

It still works fine on my end.

You will get a #Value error if anyone of the date arguments is invalid. 

- Check that your date formats are set to your region and ensure that each cell has the same date format.
- Ensure that C1 is the start date (DOB) and is placed before A1 in the formula.
- If you have typed in the formula instead of copy/pasting it from above, then ensure that there are no typing errors.
- Ensure that the relevant cells have only dates in them.

Cheerio,
vcoolio.

ARZANM · Answer

Hi Vcoolio,

I checked all the details as mentioned above and changed the format as per my region. Now I am getting error #NUM!.

What should I do. Is this happening because I am using Office 2007???

Thanks,
Arzan

vcoolio · Answer

Hello Arzanm,

DATEDIF will show a #NUM error if the Start Date (in this case, DOB) is greater than End Date (in this case, your current date). Check that this is not the case.

Using Excel 2007 could be the cause but I don't think that such a formula would make any difference. If you have access to Excel 2010, try it in 2010 just as a test.

Following is a link to my test work book which shows that it works perfectly (I use 2010):-

https://www.dropbox.com/s/146f3w7ff2vmqqg/Arzanm.xlsx?dl=0

High-light the result in D1 to see the formula in the formula bar.

Cheerio,
vcoolio.

Calculation of age

5 responses

Similar discussions

Subscribe To Our Newsletter!