I am trying to calculate the age of a person using the current "today's" date
and their birthdate without typing today's date into a cell. I do not need
the current date in a cell, due to having it in a footer. My thinking is
that it should look something like {=year(today)-year(birthdate)} I cannot
get this to work. I have their birthdates typed into a cell, but do not have
the current date.

See this:

http://cpearson.com/excel/datedif.htm

Biff

"NCCDRLEE" wrote in message
...
I am trying to calculate the age of a person using the current "today's"
date
and their birthdate without typing today's date into a cell. I do not
need
the current date in a cell, due to having it in a footer. My thinking is
that it should look something like {=year(today)-year(birthdate)} I
cannot
get this to work. I have their birthdates typed into a cell, but do not
have
the current date.

Anyone who is interested in C. Pearson's link and wonders if it is faster to use TODAY( ) instead of NOW( ) in the age calculating formula, here is a thread for additional information.

http://groups.google.ca/group/micros...c4fb16 323022

Epinn

"Biff" wrote in message ...
See this:

http://cpearson.com/excel/datedif.htm

Biff

"NCCDRLEE" wrote in message
...
I am trying to calculate the age of a person using the current "today's"
date
and their birthdate without typing today's date into a cell. I do not
need
the current date in a cell, due to having it in a footer. My thinking is
that it should look something like {=year(today)-year(birthdate)} I
cannot
get this to work. I have their birthdates typed into a cell, but do not
have
the current date.

Hi

If you only want the number of years, then you were almost there with
your formula
=YEAR(TODAY())-YEAR(A1)
where A1 is holding the birthdate.
You need to format the cell with the formula as General
FormatCellsNumberGeneral otherwise an age of say 44 would show as
13/02/1900

If you require the age split out into years, months days then follow
Biff's lead to Datedif, but do take note of some of its vagaries with
certain dates.

--
Regards

Roger Govier


"NCCDRLEE" wrote in message
...
I am trying to calculate the age of a person using the current
"today's" date
and their birthdate without typing today's date into a cell. I do not
need
the current date in a cell, due to having it in a footer. My thinking
is
that it should look something like {=year(today)-year(birthdate)} I
cannot
get this to work. I have their birthdates typed into a cell, but do
not have
the current date.

"Roger Govier" wrote in message
...

"NCCDRLEE" wrote in message
...
I am trying to calculate the age of a person using the current "today's"
date
and their birthdate without typing today's date into a cell. I do not
need
the current date in a cell, due to having it in a footer. My thinking is
that it should look something like {=year(today)-year(birthdate)} I
cannot
get this to work. I have their birthdates typed into a cell, but do not
have
the current date.

If you only want the number of years, then you were almost there with your
formula
=YEAR(TODAY())-YEAR(A1)
where A1 is holding the birthdate.
You need to format the cell with the formula as General
FormatCellsNumberGeneral otherwise an age of say 44 would show as
13/02/1900

That would give the age which will be achieved by the end of this year,
rather than the more usual usage which is the number of whole years already
achieved by today's date, which you could get by
=YEAR(TODAY()-(A1))-1900
(formatted as number with no decimal places).

If you require the age split out into years, months days then follow
Biff's lead to Datedif, but do take note of some of its vagaries with
certain dates.
--
David Biddulph

Hi David

You are absolutely correct. I had tested using my own birthday, which
has already occurred this year.
However, your formula will also give the wrong result when the month
today() is the same as the month(birthday)

=INT(YEARFRAC(birthday,today(),3)) (from the Analysis Toolpak, will
also give the wrong result because of leap years)
so maybe the best for whole year ages would be

=INT((Today()-Birthday)/365.25)

--
Regards

Roger Govier


"David Biddulph" wrote in message
...
"Roger Govier" wrote in message
...

"NCCDRLEE" wrote in message
...
I am trying to calculate the age of a person using the current
"today's" date
and their birthdate without typing today's date into a cell. I do
not need
the current date in a cell, due to having it in a footer. My
thinking is
that it should look something like {=year(today)-year(birthdate)} I
cannot
get this to work. I have their birthdates typed into a cell, but do
not have
the current date.

If you only want the number of years, then you were almost there with
your formula
=YEAR(TODAY())-YEAR(A1)
where A1 is holding the birthdate.
You need to format the cell with the formula as General
FormatCellsNumberGeneral otherwise an age of say 44 would show as
13/02/1900

That would give the age which will be achieved by the end of this
year, rather than the more usual usage which is the number of whole
years already achieved by today's date, which you could get by
=YEAR(TODAY()-(A1))-1900
(formatted as number with no decimal places).

If you require the age split out into years, months days then follow
Biff's lead to Datedif, but do take note of some of its vagaries with
certain dates.
--
David Biddulph

"Roger Govier" wrote in message
...
"David Biddulph" wrote in message
...
"Roger Govier" wrote in message
...
....
If you only want the number of years, then you were almost there with
your formula
=YEAR(TODAY())-YEAR(A1)
where A1 is holding the birthdate.
You need to format the cell with the formula as General
FormatCellsNumberGeneral otherwise an age of say 44 would show as
13/02/1900

That would give the age which will be achieved by the end of this year,
rather than the more usual usage which is the number of whole years
already achieved by today's date, which you could get by
=YEAR(TODAY()-(A1))-1900
(formatted as number with no decimal places).

Hi David

You are absolutely correct. I had tested using my own birthday, which has
already occurred this year.
However, your formula will also give the wrong result when the month
today() is the same as the month(birthday)

You've confused me there, Roger. I can't see why that should be the case
(as the calculation of the month doesn't come into my formula), and when I
test it with birthdays in October it shows the age changing for dates on or
before 12th October, as I would expect.

The only situation where I can foresee that there might be a difficulty is
around leap years, with the question of when someone born on 29th Feb
celebrates their birthday (without resorting to the Pirates of Penzance!).
--
David Biddulph

Hi David

Don't know what's going on here.
When I enter 11/10/1944 in A1
=YEAR(TODAY()-(A1))-1900
returns 61
30/09/1944 returns 62
but all October dates return 61
--
Regards

Roger Govier


"David Biddulph" wrote in message
...
"Roger Govier" wrote in message
...
"David Biddulph" wrote in message
...
"Roger Govier" wrote in message
...
...
If you only want the number of years, then you were almost there
with your formula
=YEAR(TODAY())-YEAR(A1)
where A1 is holding the birthdate.
You need to format the cell with the formula as General
FormatCellsNumberGeneral otherwise an age of say 44 would show
as 13/02/1900

That would give the age which will be achieved by the end of this
year, rather than the more usual usage which is the number of whole
years already achieved by today's date, which you could get by
=YEAR(TODAY()-(A1))-1900
(formatted as number with no decimal places).

Hi David

You are absolutely correct. I had tested using my own birthday, which
has already occurred this year.
However, your formula will also give the wrong result when the month
today() is the same as the month(birthday)

You've confused me there, Roger. I can't see why that should be the
case (as the calculation of the month doesn't come into my formula),
and when I test it with birthdays in October it shows the age changing
for dates on or before 12th October, as I would expect.

The only situation where I can foresee that there might be a
difficulty is around leap years, with the question of when someone
born on 29th Feb celebrates their birthday (without resorting to the
Pirates of Penzance!).
--
David Biddulph