Calculation of age

Closed
ARZANM
Posts
9
Registration date
Tuesday November 17, 2015
Status
Member
Last seen
December 8, 2015
- Nov 24, 2015 at 01:41 AM
vcoolio
Posts
1347
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
June 10, 2022
- Nov 25, 2015 at 02:43 AM
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!

5 replies

vcoolio
Posts
1347
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
June 10, 2022
249
Nov 24, 2015 at 04:13 AM
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.
0
ARZANM
Posts
9
Registration date
Tuesday November 17, 2015
Status
Member
Last seen
December 8, 2015

Nov 24, 2015 at 04:32 AM
Hi Vcoolio,

Thanks for your reply but its still not working. I need to get the solution as per the details mentioned below.

Cell A1: 14/11/2015
Cell C1: 19/10/1986 (Date of Birth)
Cell D1: "Age"

I hope this is clear.

Thanks in advance!
0
RayH > ARZANM
Posts
9
Registration date
Tuesday November 17, 2015
Status
Member
Last seen
December 8, 2015

Nov 24, 2015 at 11:56 AM
is this it?
=DATEDIF(B1,A1,"y")&" years, "& DATEDIF(B1,A1,"ym") & " month(s), "& DATEDIF(B1,A1,"md") &" days"


Simply changed the cell references.

(and try not to be so abrupt otherwise you wont get more responses! At least from me)
0
vcoolio
Posts
1347
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
June 10, 2022
249 > RayH
Nov 24, 2015 at 03:20 PM
Hi Ray,

You've just cnfirmed it for me (further down the page). Although C1 is the start date (DOB).
This thread is a bit jumbled!

Thanks and cheerio,
vcoolio.
0
vcoolio
Posts
1347
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
June 10, 2022
249
Nov 24, 2015 at 05:17 AM
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.
0
ARZANM
Posts
9
Registration date
Tuesday November 17, 2015
Status
Member
Last seen
December 8, 2015

Nov 24, 2015 at 06:21 AM
Hi Vcoolio,

Yes I had to change the date in cell A1 as per the new requirement. The date in this cell is not current date anymore. It will keep on varying.

The C1 cell is the DOB of the person.

The age will have to be calculated by days, months & years.

Sorry for the trouble and thanks in advance!
0
vcoolio
Posts
1347
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
June 10, 2022
249 > ARZANM
Posts
9
Registration date
Tuesday November 17, 2015
Status
Member
Last seen
December 8, 2015

Nov 24, 2015 at 06:46 AM
Hello Arzanm,

If the current date is to vary, then, wherever in the formula you see Today(), change it to your cell reference (A1).

Thus:-

=DATEDIF(C1,A1,"y")&" years, "& DATEDIF(C1,A1,"ym") & " month(s), "& DATEDIF(C1,A1,"md") &" days"

Cheerio,
vcoolio.
0
ARZANM
Posts
9
Registration date
Tuesday November 17, 2015
Status
Member
Last seen
December 8, 2015

Nov 24, 2015 at 11:37 PM
Thanks Vcoolio but I am still getting an error #VALUE

Please help

Thanks
Arzan
0
vcoolio
Posts
1347
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
June 10, 2022
249
Nov 25, 2015 at 12:14 AM
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.
0
ARZANM
Posts
9
Registration date
Tuesday November 17, 2015
Status
Member
Last seen
December 8, 2015

Nov 25, 2015 at 01:36 AM
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
0

Didn't find the answer you are looking for?

Ask a question
vcoolio
Posts
1347
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
June 10, 2022
249
Nov 25, 2015 at 02:43 AM
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.
0