calculating age based on birthdate

[Guest]

I work with student data in public schools, and want to have the spreadsheet
maintain the actual student age based on birthdate. What formula should be
used to make this occur?

Thanks.

 

[Frank Kabel]

Hi
see:
http://www.cpearson.com/excel/datedif.htm#Age

 

[Bob Phillips]

Leon,

Take a look at

http://www.cpearson.com/excel/datedif.htm#Age

 

[GerryK]

=QUOTIENT(A1,365) & " years, " & QUOTIENT(MOD(A1,365),30)
& " months and " &QUOTIENT(MOD(MOD(A1,365),30),1) & " days"

Where A1 is how old you are in days from:
[birthdate][=TODAY()][=DATEDIF(Birthdate cell,Today()
cell,"d")]...goes into A1

 

[Bob Phillips]

not exactly accurate

--

HTH

RP

=QUOTIENT(A1,365) & " years, " & QUOTIENT(MOD(A1,365),30)
& " months and " &QUOTIENT(MOD(MOD(A1,365),30),1) & " days"

Where A1 is how old you are in days from:
[birthdate][=TODAY()][=DATEDIF(Birthdate cell,Today()
cell,"d")]...goes into A1

-----Original Message-----
I work with student data in public schools, and want to have the spreadsheet
maintain the actual student age based on birthdate. What formula should be
used to make this occur?

Thanks.
.

 

[GerryK]

Formulae results are approximate because of the 365
assumption.

-----Original Message-----
not exactly accurate

--

HTH

RP

=QUOTIENT(A1,365) & " years, " & QUOTIENT(MOD (A1,365),30)
& " months and " "IENT(MOD(MOD(A1,365),30),1) & " days"

Where A1 is how old you are in days from:
[birthdate][=TODAY()][=DATEDIF(Birthdate cell,Today()
cell,"d")]...goes into A1

-----Original Message-----
I work with student data in public schools, and want to have the spreadsheet
maintain the actual student age based on birthdate.

What
formula should be

used to make this occur?

Thanks.
.

.

 

[Myrna Larson]

Sorry, but no cigar! <g>

Dividing by 365 isn't correct: some years have 366 days. The older the person,
the bigger the error due to this problem. After 60 years, you are off by ~15
days. Mod 30 also isn't correct: some months have 31 days, February 28 or 29.

Today is 25 Oct 2004. Using a birthdate of 25 Oct 1954, your formula gives 50
years and 13 days. Should be 50 years, 0 months, 0 days.

Why not just leave the birthdate alone and use DATEDIF for the entire formula?
I realize this is harder without the documentation for DATEDIF and knowledge
of all the possibilities for the 3rd argument. Nevertheless,

=DATEDIF(A2,TODAY(),"y")&" years, "&DATEDIF(A2,TODAY(),"ym")&" months and
"&DATEDIF(A2,TODAY(),"md")& " days"

With the date 25 Oct 2004 in A2, that gives the correct age. But even DATEDIF
has problems when the one or both of the dates are close to the end of the
month, and the 2nd is close to the birthday.

=QUOTIENT(A1,365) & " years, " & QUOTIENT(MOD(A1,365),30)
& " months and " &QUOTIENT(MOD(MOD(A1,365),30),1) & " days"

Where A1 is how old you are in days from:
[birthdate][=TODAY()][=DATEDIF(Birthdate cell,Today()
cell,"d")]...goes into A1

-----Original Message-----
I work with student data in public schools, and want to have the spreadsheet
maintain the actual student age based on birthdate. What formula should be
used to make this occur?

Thanks.
.

 

[Myrna Larson]

Which you don't need to make because Excel includes functions to deal with
this, namely DATEDIF.

Formulae results are approximate because of the 365
assumption.

-----Original Message-----
not exactly accurate

--

HTH

RP

=QUOTIENT(A1,365) & " years, " & QUOTIENT(MOD (A1,365),30)
& " months and " "IENT(MOD(MOD(A1,365),30),1) & " days"

Where A1 is how old you are in days from:
[birthdate][=TODAY()][=DATEDIF(Birthdate cell,Today()
cell,"d")]...goes into A1

-----Original Message-----
I work with student data in public schools, and want to
have the spreadsheet
maintain the actual student age based on birthdate. What
formula should be
used to make this occur?

Thanks.
.

.

 

[Guest]

nYou could change the 365 in my original post to Earth's orbit value in days
cosidering this:
365 days
6 hrs.
9 min.
9.54 sec
to get real exact and not loose any time!

Sorry, but no cigar! <g>

Dividing by 365 isn't correct: some years have 366 days. The older the person,
the bigger the error due to this problem. After 60 years, you are off by ~15
days. Mod 30 also isn't correct: some months have 31 days, February 28 or 29.

Today is 25 Oct 2004. Using a birthdate of 25 Oct 1954, your formula gives 50
years and 13 days. Should be 50 years, 0 months, 0 days.

Why not just leave the birthdate alone and use DATEDIF for the entire formula?
I realize this is harder without the documentation for DATEDIF and knowledge
of all the possibilities for the 3rd argument. Nevertheless,

=DATEDIF(A2,TODAY(),"y")&" years, "&DATEDIF(A2,TODAY(),"ym")&" months and
"&DATEDIF(A2,TODAY(),"md")& " days"

With the date 25 Oct 2004 in A2, that gives the correct age. But even DATEDIF
has problems when the one or both of the dates are close to the end of the
month, and the 2nd is close to the birthday.

=QUOTIENT(A1,365) & " years, " & QUOTIENT(MOD(A1,365),30)
& " months and " "IENT(MOD(MOD(A1,365),30),1) & " days"

Where A1 is how old you are in days from:
[birthdate][=TODAY()][=DATEDIF(Birthdate cell,Today()
cell,"d")]...goes into A1

-----Original Message-----
I work with student data in public schools, and want to have the spreadsheet
maintain the actual student age based on birthdate. What formula should be
used to make this occur?

Thanks.
.

 

[Myrna Larson]

Thanks for the offer, but the DATEDIF formula is quite a bit less hassle.
That's what it's there for.

But then you have another problem which you haven't addressed: you choose to
say that all months have 30 days. Not correct. What's your suggestion for that
issue?

nYou could change the 365 in my original post to Earth's orbit value in days
cosidering this:
365 days
6 hrs.
9 min.
9.54 sec
to get real exact and not loose any time!

Sorry, but no cigar! <g>

Dividing by 365 isn't correct: some years have 366 days. The older the person,
the bigger the error due to this problem. After 60 years, you are off by ~15
days. Mod 30 also isn't correct: some months have 31 days, February 28 or 29.

Today is 25 Oct 2004. Using a birthdate of 25 Oct 1954, your formula gives 50
years and 13 days. Should be 50 years, 0 months, 0 days.

Why not just leave the birthdate alone and use DATEDIF for the entire formula?
I realize this is harder without the documentation for DATEDIF and knowledge
of all the possibilities for the 3rd argument. Nevertheless,

=DATEDIF(A2,TODAY(),"y")&" years, "&DATEDIF(A2,TODAY(),"ym")&" months and
"&DATEDIF(A2,TODAY(),"md")& " days"

With the date 25 Oct 2004 in A2, that gives the correct age. But even DATEDIF
has problems when the one or both of the dates are close to the end of the
month, and the 2nd is close to the birthday.

=QUOTIENT(A1,365) & " years, " & QUOTIENT(MOD(A1,365),30)
& " months and " "IENT(MOD(MOD(A1,365),30),1) & " days"

Where A1 is how old you are in days from:
[birthdate][=TODAY()][=DATEDIF(Birthdate cell,Today()
cell,"d")]...goes into A1

-----Original Message-----
I work with student data in public schools, and want to
have the spreadsheet
maintain the actual student age based on birthdate. What
formula should be
used to make this occur?

Thanks.
.

 

[Myrna Larson]

On second thought, your latest suggestion won't work, either. The year, month
and day change at midnight of the appropriate date. The new year begins on Jan
1, at 0:00:00, not at 6:09:9.54 on January 1. And most people use the ordinary
calendar to calculate ages, don't they?

I don't think you can wiggle your way out of your "non-solution" <g>. It
doesn't work, period. You have to go by the calendar, not by average (or most
common) lengths of years and months.

 

[Bob Phillips]

and for a 3 year old experienced a leap year?

--

HTH

RP

Thanks for the offer, but the DATEDIF formula is quite a bit less hassle.
That's what it's there for.

But then you have another problem which you haven't addressed: you choose to
say that all months have 30 days. Not correct. What's your suggestion for that

issue?



nYou could change the 365 in my original post to Earth's orbit value in days
cosidering this:
365 days
6 hrs.
9 min.
9.54 sec
to get real exact and not loose any time!

by

~15

or

29.

gives

50

years and 13 days. Should be 50 years, 0 months, 0 days.

Why not just leave the birthdate alone and use DATEDIF for the entire formula?
I realize this is harder without the documentation for DATEDIF and knowledge
of all the possibilities for the 3rd argument. Nevertheless,

=DATEDIF(A2,TODAY(),"y")&" years, "&DATEDIF(A2,TODAY(),"ym")&" months and
"&DATEDIF(A2,TODAY(),"md")& " days"

With the date 25 Oct 2004 in A2, that gives the correct age. But even DATEDIF
has problems when the one or both of the dates are close to the end of the
month, and the 2nd is close to the birthday.


On Mon, 25 Oct 2004 12:30:02 -0700, "GerryK"

=QUOTIENT(A1,365) & " years, " & QUOTIENT(MOD(A1,365),30)
& " months and " "IENT(MOD(MOD(A1,365),30),1) & " days"

Where A1 is how old you are in days from:
[birthdate][=TODAY()][=DATEDIF(Birthdate cell,Today()
cell,"d")]...goes into A1

-----Original Message-----
I work with student data in public schools, and want to
have the spreadsheet
maintain the actual student age based on birthdate. What
formula should be
used to make this occur?

Thanks.
.

 

[Myrna Larson]

His/her formula just won't work, but he/she seems reluctant to admit that.

 

[GerryK]

replace the 30 with 30.43803003!

-----Original Message-----
His/her formula just won't work, but he/she seems reluctant to admit that.



.

 

[Myrna Larson]

Again, people calculate ages based on the calendar. The age changes at
*midnight* on the anniversary of the birthdate. It doesn't change at some
variable time of the day -- variable based on non-integer days in a year and
non-integer days in a month.

This is a real-world problem, not an astronomy calculation, remember?

 

[Myrna Larson]

Using GerryK's new values for length of year and length of month
(365+6/24+9/1440+9.54/86400 and 30.43803003, respectively) with a birthdate of
Jan 1, 1920, and 2nd date of Jan 1, 2004, the result is 83 years, 11 mos, 29
days. The original formula gave 84 years, 21 days. The new constants are
better, but the result is still not correct, and not usable in any business
situation that I am familiar with.

Averages just don't work for this problem: when the 2nd date is very close to
the birthday, results from such formulas may have the age change a day or two
early or a day or two late. You have do to it the way a human would, but
looking at the calendar.

 

[GerryK]

I coming from an astronomical background and I agree that
there is an change up every 4 years in the formula day
portion. If that is a concern you can check the status of
the day by:
=A1-INT(A1/I10)*I10-INT((A1-INT(A1/I10)*I10)/I11)*I11
where I10=365.256360416667 and I11=30.4380300347222
So another way of doing this is in a separated fashion:
For the year use =INT(A1/I10)
month use =INT((A1-INT(A1/I10)*I10)/I11)
day use =A1-INT(A1/I10)*I10-INT((A1-INT(A1/I10)*I10)/I11)
*I11. You can visually round this number up as you need.
(A1 is how many days old, calculated from =DATEDIF
(birthday,Today(),"d").

Is a newborn 1 day old at birth or 0 days old?

-----Original Message-----
Using GerryK's new values for length of year and length of month
(365+6/24+9/1440+9.54/86400 and 30.43803003,

respectively) with a birthdate of

 

[Bob Phillips]

Myrna,

When that wall falls, your head is going to feel so good<vbg>

Bob

 

[Myrna Larson]

You STILL miss the point. This ISN'T an astronomical question. It's a business
question.

Before computers were available, people calculated ages by looking at a
calendar, where years have either 365 or 366 days, months have 28, 29, 30, or
31 days, and all of those numbers are integers.

If you want an accurate answer *TO THE DAY* (which is what your formula
purports to do, but doesn't), you CANNOT use averages.

If you are calculating the date at which a person becomes eligible for a
pension, or for some other benefit like insurance subsidy, etc, you can't be
"off" by a day or two. That won't sell.

As for the age on the date of birth, that's a convention that is solved by
subtracting 1 from the birthdate or adding 1 to the 2nd date, according to the
user's business rules.

 

[Myrna Larson]

Yes, I know <g>. The situation is hopeless.

I just hope Gerry doesn't apply for a job that requires determining when
somebody is eligible for pension, insurance benefits, etc.