Jimmy Joseph said:
I wanted to calculate age of a candidate in Year/Month/Day format. What
is function to be used in Excel to get the result in Year/Month/Day
format. I have the date of birth of the candidate and wanted to have
the result for a given date.
eg. Date of birth of candidate = 20.05.1976
what will be the age of the candidate on 20.10.2005 in Year/Month/day
format
Another way you could do this is with DateDif (see
http://cpearson.com/excel/datedif.htm
for documentation). For instance, say the birth date is in A1 and the
date of interest is in B1:
=DATEDIF(A1,B1,"y") & "/" & DATEDIF(A1,B1,"m") & "/" &
DATEDIF(A1,B1,"md")
However, note that "month" is a tricky concept. For instance how many
months and days is it between 31.1.2005 and 2.3.2005 (30 days)?
The formula above will say 0/1/-1. Since the first date is in January
and January has 31 days, it calculates 1 thirty-one-day month (3.3.2005)
with -1 day.
There are other results that make sense, but nothing can be made
consistent since the concept of month is so variable. For instance, if
you say one month after 31.1.2005 is 28.2.2005, what should be the
result for one month after 29.1.2005?
Any algorithm you devise will have to deal with this type of anomaly.
OTOH, you could use years and days:
=DATEDIF(A1,B1,"y") & "/" & DATEDIF(A1,B1,"yd")
which is perfectly self-consistent for all but leap days (using days
alone would be absolutely consistent).
