Calculating Age
 

If I have a person's birthday, how can I calculate their current age based on
today's date?


Thanks!

You can use the DATEDIF function. E.g.,

=DATEDIF(birthdate,TODAY(),"y")

See www.cpearson.com/excel/datedif.htm for more information about
DATEDIF.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"devinm21" wrote in message
...
If I have a person's birthday, how can I calculate their
current age based on
today's date?


Thanks!

"devinm21" wrote:

If I have a person's birthday, how can I calculate their current age based on
today's date?


Thanks!

"ertug" wrote in message
...


"devinm21" wrote:

If I have a person's birthday, how can I calculate their current age
based on
today's date?


Thanks!


In whole years:

=YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1),1,IF(DAY(NOW())<DAY(A1),1,0)))

On Thu, 9 Jun 2005 04:54:09 -0500, "Vic Sowers"
wrote:


"ertug" wrote in message
...


"devinm21" wrote:

If I have a person's birthday, how can I calculate their current age
based on
today's date?


Thanks!


In whole years:


Somewhat shorter formula:

=DATEDIF(A1,TODAY(),"y")


--ron

There's been a big discussion on this already.

http://excelforum.com/showthread.php...light=bluenose

Mangesh



"Vic Sowers" wrote in message
...

"ertug" wrote in message
...


"devinm21" wrote:

If I have a person's birthday, how can I calculate their current age
based on
today's date?


Thanks!


In whole years:


=YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1),
1,IF(DAY(NOW())<DAY(A1),1,0)))

My version of Excel does not have the "DATEIF" function. I was bale to cut
and paste the formula in, and it works fine. However it dispays the results
as Xyears, Xmonths, Xdays. I used to have it displayed as a four digit
(##.##) number, which made finding the average age eay. Any suggestions on
how to get that format back?

"Mangesh Yadav" wrote:

There's been a big discussion on this already.

http://excelforum.com/showthread.php...light=bluenose

Mangesh



"Vic Sowers" wrote in message
...

"ertug" wrote in message
...


"devinm21" wrote:

If I have a person's birthday, how can I calculate their current age
based on
today's date?


Thanks!


In whole years:


=YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1),
1,IF(DAY(NOW())<DAY(A1),1,0)))

Then you simply need to subtract the earlier date from the recent, and
format as number.
=A1-B1

Mangesh



"junepbug" wrote in message
...
My version of Excel does not have the "DATEIF" function. I was bale to cut
and paste the formula in, and it works fine. However it dispays the
results
as Xyears, Xmonths, Xdays. I used to have it displayed as a four digit
(##.##) number, which made finding the average age eay. Any suggestions on
how to get that format back?

"Mangesh Yadav" wrote:

There's been a big discussion on this already.

http://excelforum.com/showthread.php...light=bluenose

Mangesh



"Vic Sowers" wrote in message
...

"ertug" wrote in message
...


"devinm21" wrote:

If I have a person's birthday, how can I calculate their current
age
based on
today's date?


Thanks!


In whole years:



=YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1),
1,IF(DAY(NOW())<DAY(A1),1,0)))

It comes up as 5545 instead of 58 yrs,7 mos. (58.58). I tried to change the
formating but it's not coming up correctly. Is there anything else I can do?

"Mangesh Yadav" wrote:

Then you simply need to subtract the earlier date from the recent, and
format as number.
=A1-B1

Mangesh



"junepbug" wrote in message
...
My version of Excel does not have the "DATEIF" function. I was bale to cut
and paste the formula in, and it works fine. However it dispays the
results
as Xyears, Xmonths, Xdays. I used to have it displayed as a four digit
(##.##) number, which made finding the average age eay. Any suggestions on
how to get that format back?

"Mangesh Yadav" wrote:

There's been a big discussion on this already.

http://excelforum.com/showthread.php...light=bluenose

Mangesh



"Vic Sowers" wrote in message
...

"ertug" wrote in message
...


"devinm21" wrote:

If I have a person's birthday, how can I calculate their current
age
based on
today's date?


Thanks!


In whole years:



=YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1),
1,IF(DAY(NOW())<DAY(A1),1,0)))

Could you post your data.
And the formula you are using.

Mangesh



"junepbug" wrote in message
...
It comes up as 5545 instead of 58 yrs,7 mos. (58.58). I tried to change
the
formating but it's not coming up correctly. Is there anything else I can
do?

"Mangesh Yadav" wrote:

Then you simply need to subtract the earlier date from the recent, and
format as number.
=A1-B1

Mangesh



"junepbug" wrote in message
...
My version of Excel does not have the "DATEIF" function. I was bale to
cut
and paste the formula in, and it works fine. However it dispays the
results
as Xyears, Xmonths, Xdays. I used to have it displayed as a four digit
(##.##) number, which made finding the average age eay. Any
suggestions on
how to get that format back?

"Mangesh Yadav" wrote:

There's been a big discussion on this already.

http://excelforum.com/showthread.php...light=bluenose

Mangesh



"Vic Sowers" wrote in message
...

"ertug" wrote in message
...


"devinm21" wrote:

If I have a person's birthday, how can I calculate their
current
age
based on
today's date?


Thanks!


In whole years:




=YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1),
1,IF(DAY(NOW())<DAY(A1),1,0)))

=DATEDIF(I2,(H2),"y") & " years, " & DATEDIF(I2,(H2),"ym") & "
months, " & DATEDIF(I2,(H2),"md") & " days"

In this case I2 = 11/2/1964 and H2 = 5/2/1996. Formatting cells as a 2
decimal place number fills in "31 years, 6 months, 0 days". Formatting any
other way doesn't return an age.
There was a way to do a "yearif" formula, however since I have had an
upgrade I cannot find that formula. The "yearif" returned 31.50.

Thanks!

"Mangesh Yadav" wrote:

Could you post your data.
And the formula you are using.

Mangesh



"junepbug" wrote in message
...
It comes up as 5545 instead of 58 yrs,7 mos. (58.58). I tried to change
the
formating but it's not coming up correctly. Is there anything else I can
do?

"Mangesh Yadav" wrote:

Then you simply need to subtract the earlier date from the recent, and
format as number.
=A1-B1

Mangesh



"junepbug" wrote in message
...
My version of Excel does not have the "DATEIF" function. I was bale to
cut
and paste the formula in, and it works fine. However it dispays the
results
as Xyears, Xmonths, Xdays. I used to have it displayed as a four digit
(##.##) number, which made finding the average age eay. Any
suggestions on
how to get that format back?

"Mangesh Yadav" wrote:

There's been a big discussion on this already.

http://excelforum.com/showthread.php...light=bluenose

Mangesh



"Vic Sowers" wrote in message
...

"ertug" wrote in message
...


"devinm21" wrote:

If I have a person's birthday, how can I calculate their
current
age
based on
today's date?


Thanks!


In whole years:




=YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1),
1,IF(DAY(NOW())<DAY(A1),1,0)))

Ok. So you are looking for the YEARFRAC function.

=YEARFRAC(I2,H2)

which returns 31.5


Mangesh




"junepbug" wrote in message
...
=DATEDIF(I2,(H2),"y") & " years, " & DATEDIF(I2,(H2),"ym") & "
months, " & DATEDIF(I2,(H2),"md") & " days"

In this case I2 = 11/2/1964 and H2 = 5/2/1996. Formatting cells as a 2
decimal place number fills in "31 years, 6 months, 0 days". Formatting any
other way doesn't return an age.
There was a way to do a "yearif" formula, however since I have had an
upgrade I cannot find that formula. The "yearif" returned 31.50.

Thanks!

"Mangesh Yadav" wrote:

Could you post your data.
And the formula you are using.

Mangesh



"junepbug" wrote in message
...
It comes up as 5545 instead of 58 yrs,7 mos. (58.58). I tried to
change
the
formating but it's not coming up correctly. Is there anything else I
can
do?

"Mangesh Yadav" wrote:

Then you simply need to subtract the earlier date from the recent,
and
format as number.
=A1-B1

Mangesh



"junepbug" wrote in message
...
My version of Excel does not have the "DATEIF" function. I was
bale to
cut
and paste the formula in, and it works fine. However it dispays
the
results
as Xyears, Xmonths, Xdays. I used to have it displayed as a four
digit
(##.##) number, which made finding the average age eay. Any
suggestions on
how to get that format back?

"Mangesh Yadav" wrote:

There's been a big discussion on this already.

http://excelforum.com/showthread.php...light=bluenose

Mangesh



"Vic Sowers" wrote in message
...

"ertug" wrote in message
...


"devinm21" wrote:

If I have a person's birthday, how can I calculate their
current
age
based on
today's date?


Thanks!


In whole years:





=YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1),
1,IF(DAY(NOW())<DAY(A1),1,0)))

Forgot to mention that you need the Analysis Toolpak to use the YEARFRAC
function.

Mangesh


"junepbug" wrote in message
...
=DATEDIF(I2,(H2),"y") & " years, " & DATEDIF(I2,(H2),"ym") & "
months, " & DATEDIF(I2,(H2),"md") & " days"

In this case I2 = 11/2/1964 and H2 = 5/2/1996. Formatting cells as a 2
decimal place number fills in "31 years, 6 months, 0 days". Formatting any
other way doesn't return an age.
There was a way to do a "yearif" formula, however since I have had an
upgrade I cannot find that formula. The "yearif" returned 31.50.

Thanks!

"Mangesh Yadav" wrote:

Could you post your data.
And the formula you are using.

Mangesh



"junepbug" wrote in message
...
It comes up as 5545 instead of 58 yrs,7 mos. (58.58). I tried to
change
the
formating but it's not coming up correctly. Is there anything else I
can
do?

"Mangesh Yadav" wrote:

Then you simply need to subtract the earlier date from the recent,
and
format as number.
=A1-B1

Mangesh



"junepbug" wrote in message
...
My version of Excel does not have the "DATEIF" function. I was
bale to
cut
and paste the formula in, and it works fine. However it dispays
the
results
as Xyears, Xmonths, Xdays. I used to have it displayed as a four
digit
(##.##) number, which made finding the average age eay. Any
suggestions on
how to get that format back?

"Mangesh Yadav" wrote:

There's been a big discussion on this already.

http://excelforum.com/showthread.php...light=bluenose

Mangesh



"Vic Sowers" wrote in message
...

"ertug" wrote in message
...


"devinm21" wrote:

If I have a person's birthday, how can I calculate their
current
age
based on
today's date?


Thanks!


In whole years:





=YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1),
1,IF(DAY(NOW())<DAY(A1),1,0)))