In Excel, how do I convert a date of birth to actual age as of today's date?

Question

I manage various spreadsheets on excel and I need a set of data or a formula which provides information on the time covered from date of first entry (date birth) in one cell to today (present day) in another cell, expressed in Years, months and days.

Answer 1

=YEAR(NOW())-YEAR(A1) & " years, " & MONTH(NOW())-MONTH(A1) & " months and " & DAY(NOW())-DAY(A1) & " days"

Answer 2

I don't think there's a direct method. To get the number of days use the following formula: (say the date of birth is in cell A1) =today()-A1

then do some simple formula like dividing the result by 365 to get the years

Answer 3

Very simple if you use cell formatting. To access cell formatting right click on a cell and select formatting.

Format cell A1 as MM/dd/yy by using the predefined DATE format.

In the adjacen cell enter the formula =Today() - A1

Now format this cell but select CUSTOM formatting and in the type box replace the contents with yy.

yy will display years 0 to 99

YOu can copy this into two adjacent cells and format each using mm and dd as custom strings. OR use a single cell and use a custom format string yy mm dd

Answer 4

Its a bit of a mess, but it will work.

Basically, you need to split the date into day, month and year.
Say the date is in cell A1
make
B1 '=year(A1)'
C1 '=month(A1)'
D1 '=day(A1)'

This will split the date into 3 cells. Do the same for the second date and then subtract the year parts from each date, the month parts and the day parts to give the difference and get years, months and days difference.

Post additional if you need more explanation.

Answer 5

Assuming your TEXT-DATE is in cell A1, use formula =DATE(2010, MID(A1, 4, 2), LEFT(A1, 2) ) If your data DATE is Date Type (not Text Type), the formula is: =Date(2010, Month(A1), Day(A1) )