I have an excel spreadsheet which contains a column (B) which shows dates of
birth, marriages etc in the format dd/mm/yy. Across row 1 starting at column
c is the year 2010, in column d 2011, column d 2012 etc, what I would like is
a formula to calculate the age of the person concerned or anniversary using
these two criteria. I know it can be done as I have asked this previously,
but unfortunately, I cannot find the post. I will be grateful for any help
that may be given.

Suppose B2 has 31/3/1978 (the actual format is not important)
In C2 enter =DATEDIF(B2,DATE(C$1,MONTH($B2),DAY($B2)),"y")
to find the number of years between B2's date and the same day in C1's year
(in this case the years between 31/3/1978 and 31/3/2010)

The $'s allow this formula to be copied across the row and down the table
More info on DATEDIF see
http://www.cpearson.com/excel/datedif.aspx
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Chris waller" wrote in message
...
I have an excel spreadsheet which contains a column (B) which shows dates
of
birth, marriages etc in the format dd/mm/yy. Across row 1 starting at
column
c is the year 2010, in column d 2011, column d 2012 etc, what I would like
is
a formula to calculate the age of the person concerned or anniversary
using
these two criteria. I know it can be done as I have asked this previously,
but unfortunately, I cannot find the post. I will be grateful for any
help
that may be given.

And as an alternative, that is presumably the same as =YEAR(C$1)-YEAR(B2) ?
--
David Biddulph


"Bernard Liengme" wrote in message
...
Suppose B2 has 31/3/1978 (the actual format is not important)
In C2 enter =DATEDIF(B2,DATE(C$1,MONTH($B2),DAY($B2)),"y")
to find the number of years between B2's date and the same day in C1's
year (in this case the years between 31/3/1978 and 31/3/2010)

The $'s allow this formula to be copied across the row and down the table
More info on DATEDIF see
http://www.cpearson.com/excel/datedif.aspx
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Chris waller" wrote in message
...
I have an excel spreadsheet which contains a column (B) which shows dates
of
birth, marriages etc in the format dd/mm/yy. Across row 1 starting at
column
c is the year 2010, in column d 2011, column d 2012 etc, what I would
like is
a formula to calculate the age of the person concerned or anniversary
using
these two criteria. I know it can be done as I have asked this
previously,
but unfortunately, I cannot find the post. I will be grateful for any
help
that may be given.

Well if you want to be minimalist about it <grin (did not see the tree for
the forest)
But I think it would be =C$1-YEAR($B2) since C1 has 4-digit number not a
date.
Happy Spring
Bernard
"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
And as an alternative, that is presumably the same as =YEAR(C$1)-YEAR(B2)
?
--
David Biddulph


"Bernard Liengme" wrote in message
...
Suppose B2 has 31/3/1978 (the actual format is not important)
In C2 enter =DATEDIF(B2,DATE(C$1,MONTH($B2),DAY($B2)),"y")
to find the number of years between B2's date and the same day in C1's
year (in this case the years between 31/3/1978 and 31/3/2010)

The $'s allow this formula to be copied across the row and down the table
More info on DATEDIF see
http://www.cpearson.com/excel/datedif.aspx
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Chris waller" wrote in message
...
I have an excel spreadsheet which contains a column (B) which shows
dates of
birth, marriages etc in the format dd/mm/yy. Across row 1 starting at
column
c is the year 2010, in column d 2011, column d 2012 etc, what I would
like is
a formula to calculate the age of the person concerned or anniversary
using
these two criteria. I know it can be done as I have asked this
previously,
but unfortunately, I cannot find the post. I will be grateful for any
help
that may be given.