Returning an age

[Guest]

Hi

I have a date of birth in column A such as 01/01/1950, is there a way i can
get it to show me the age in years say in column B. Compared to the date
today(that updates) or a fixed date such as 01/01/2007?

Many Thanks

 

[papou]

Hi Neil
With date of birth in A1, formula in B1:
=INT((NOW()-A1)/365,25)&" years"

HTH
Cordially
Pascal

 

[Guest]

Hi

Thanks very much for that Papou
But i can't sem to get it to work. Maybe i am putting it in wrong but it
doesn't seem to like the 25.

Thanks though

 

[Guest]

Dear Neil,

This works for me:

Simple method:
Date of Birth in Cell A1 = 01-Jan-1950
Today or any day in Year 2007 will be 57 years old.

In Cell B1 =YEAR(TODAY())-YEAR(A1)


More stuff:
Date of Birth in Cell A1 = 01-Jan-1950
Today, the date of birth will be approximately 57years 8 months

In Cell B1 =INT(DAYS360(A1,TODAY())/360-MOD(DAYS360(A1,TODAY())/360,1))

In Cell C1 =INT(ROUND(MOD(DAYS360(A2,TODAY())/360,1)*12,0))

B1 = 57
c1 = 8

HTH.

 

[papou]

Neil
Replace 365,25 with 365.25

HTH
Cordially
Pascal

 

[Guest]

Or Try This
Enter Date of Birth In Cell A1

And Paste this formula in any Cell
=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " &
DATEDIF(A1,NOW(),"md") & " days"

Claude

 

[David Biddulph]

That'll be because you're speaking a different language. Papou will
presumably have been using a comma as a decimal point, but you probably want
a full stop, so try
=INT((NOW()-A1)/365,25)&" years"

Another option would be
=DATEDIF(A1,TODAY(),"y")&" years"

 

[David Biddulph]

The problem with that is that if you put in a birthday of 1-Dec-1950, it
will again show 57 years old using that formula (for any date in 2007),
whereas he would currently still be only 56.

 

[Guest]

Dear Claude,

From my posting, I had to do lots of stuff to get something close or just
barely near. I can't find DatedIF in my XLS 2000. Where did you get it from?
<blur>

 

[Guest]

I am using Excel 2000 too and there is a help available on this

Try using your Office assistant and Search for datedif()

Below is the content shown in help



DATEDIF
See also

Calculates the number of days, months, or years between two dates. This
function is provided for compatibility with Lotus 1-2-3.

Syntax

DATEDIF(start_date,end_date,unit)

Start_date is a date that represents the first, or starting, date of the
period. Dates may be entered as text strings within quotation marks (for
example, "2001/1/30"), as serial numbers (for example, 36921, which
represents January 30, 2001, if you're using the 1900 date system), or as the
results of other formulas or functions (for example, DATEVALUE("2001/1/30")).
For more information about date serial numbers, see NOW.

End_date is a date that represents the last, or ending, date of the period.

Unit is the type of information you want returned.

Unit Returns
"Y" The number of complete years in the period.
"M" The number of complete months in the period.
"D" The number of days in the period.
"MD" The difference between the days in start_date and end_date. The months
and years of the dates are ignored.
"YM" The difference between the months in start_date and end_date. The days
and years of the dates are ignored.
"YD" The difference between the days of start_date and end_date. The years
of the dates are ignored.


Remarks

Microsoft Excel stores dates as sequential serial numbers so that it can
perform calculations on them. Excel stores January 1, 1900, as serial number
1 if your workbook uses the 1900 date system. If your workbook uses the 1904
date system, Excel stores January 1, 1904, as serial number 0 (January 2,
1904, is serial number 1). For example, in the 1900 date system, Excel stores
January 1, 1998, as serial number 35796 because it is 35,795 days after
January 1, 1900. Learn more about how Microsoft Excel stores dates and times.


Excel for Windows and Excel for the Macintosh use different date systems as
their default. For more information, see NOW.
Examples

DATEDIF("2001/1/1","2003/1/1","Y") equals 2, or two complete years in the
period.

DATEDIF("2001/6/1","2002/8/15","D") equals 440, or 440 days between June 1,
2001, and August 15, 2002.

DATEDIF("2001/6/1","2002/8/15","YD") equals 75, or 75 days between June 1
and August 15, ignoring the years of the dates.

DATEDIF("2001/6/1","2002/8/15","MD") equals 14, or the difference between 1
and 15 — the day of start_date and the day of end_date — ignoring the
months and the years of the dates.

 

[David Biddulph]

Excel 2000 is the one version in which Excel help *does* include the DATEDIF
function. For other Excel versions, although the function is in Excel it is
the one function which Help omits (for an unknown reason), so the usual
source of information is http://www.cpearson.com/excel/datedif.htm

Note that it is not "DatedIF" (a derivative of IF), but DATEDIF, in other
words a DATE DIFference.

 

[Guest]

YOu can use this formula to calculate age:

=ROUND((TODAY()-A1)/365.254,2)

If you prefer not to round it to 2 decimal places use the following:

=(TODAY()-A1)/365.254

Formula assumes the date is in cell A1

 

[Guest]

Thanks very much for all your replys.

I got it working as has saved me a lot of time

Thanks again

 

[Guest]

Thanks Claude & David.

That really cleared my mind. Helpful.