date of birth age formula
 

I need to work out ages from dates of birth I have on an Excel spreadsheet.
Ideally I would like to calculate age ranges, e.g. 16-20, 21-30, 31-40,
41-50, 51-60, 60+.

I am relatively new to using formulae on Excel and have tried using the tips
already posted on this site but have not been able to get this to work!

Calculating Age

You can use the DATEDIF to calculate a person's age. For example, the
formula below will calculate the age of a person as of the current date,
where BirthDate is the person's date of birth.

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


I have the TODAY formula in N, and the Dates of birth are in column E. I
just need to know where to add the data and a real step-by-step guide for an
Excel dummy! If someone can help me with this that would be great :-)

date of birth age formula
 

Donna,

Try this with birthdates in column E and Today() in N1

=SUMPRODUCT(--(DATEDIF(E1:E20,N1,"y")=16),--(DATEDIF(E1:E20,N1,"y")<=20))

This formula does the 16 - 20 year olds so change the 2 ages to get
different ranges.

Mike

"Donna" wrote:

I need to work out ages from dates of birth I have on an Excel spreadsheet.
Ideally I would like to calculate age ranges, e.g. 16-20, 21-30, 31-40,
41-50, 51-60, 60+.

I am relatively new to using formulae on Excel and have tried using the tips
already posted on this site but have not been able to get this to work!

Calculating Age

You can use the DATEDIF to calculate a person's age. For example, the
formula below will calculate the age of a person as of the current date,
where BirthDate is the person's date of birth.

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


I have the TODAY formula in N, and the Dates of birth are in column E. I
just need to know where to add the data and a real step-by-step guide for an
Excel dummy! If someone can help me with this that would be great :-)

date of birth age formula
 

I should have pointed out that 60 requires a sllightly different approach

=SUMPRODUCT(--(E1:E20<""),--(DATEDIF(E1:E20,N1,"y")60))

Mike

"Donna" wrote:

I need to work out ages from dates of birth I have on an Excel spreadsheet.
Ideally I would like to calculate age ranges, e.g. 16-20, 21-30, 31-40,
41-50, 51-60, 60+.

I am relatively new to using formulae on Excel and have tried using the tips
already posted on this site but have not been able to get this to work!

Calculating Age

You can use the DATEDIF to calculate a person's age. For example, the
formula below will calculate the age of a person as of the current date,
where BirthDate is the person's date of birth.

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


I have the TODAY formula in N, and the Dates of birth are in column E. I
just need to know where to add the data and a real step-by-step guide for an
Excel dummy! If someone can help me with this that would be great :-)

date of birth age formula
 

As a general formula you can use:

=FLOOR(DATEDIF(E5,TODAY(),"y"),10)+1&" -
"&CEILING(DATEDIF(E5,TODAY(),"y"),10)

However this will return 11 - 20 for people under 21 and 61 - 70 for people
over 60. If you want 16 - 20 and 60+ then use:

=IF(DATEDIF(E5,TODAY(),"y")<16,"Under
16",IF(DATEDIF(E5,TODAY(),"y")<21,"16 -
20",IF(DATEDIF(E5,TODAY(),"y")60,"60+",FLOOR(DATE DIF(E5,TODAY(),"y"),10)+1&"
- "&CEILING(DATEDIF(E5,TODAY(),"y"),10))))

If you want notification in columns of age ranges then for example in the
31-40 column use:

=IF(CEILING(DATEDIF(E5,TODAY(),"y"),10)=40,"In this range","")

Changing "In this range" to your choice of text.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Donna" wrote in message
...
I need to work out ages from dates of birth I have on an Excel spreadsheet.
Ideally I would like to calculate age ranges, e.g. 16-20, 21-30, 31-40,
41-50, 51-60, 60+.

I am relatively new to using formulae on Excel and have tried using the
tips
already posted on this site but have not been able to get this to work!

Calculating Age

You can use the DATEDIF to calculate a person's age. For example, the
formula below will calculate the age of a person as of the current date,
where BirthDate is the person's date of birth.

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


I have the TODAY formula in N, and the Dates of birth are in column E. I
just need to know where to add the data and a real step-by-step guide for
an
Excel dummy! If someone can help me with this that would be great :-)

date of birth age formula
 

Thanks both but where should I enter the formula? Have tried entering into
the formula bar for a cell, but this just results in #VALUE appearing in the
cell.

"Mike H" wrote:

I should have pointed out that 60 requires a sllightly different approach

=SUMPRODUCT(--(E1:E20<""),--(DATEDIF(E1:E20,N1,"y")60))

Mike

"Donna" wrote:

I need to work out ages from dates of birth I have on an Excel spreadsheet.
Ideally I would like to calculate age ranges, e.g. 16-20, 21-30, 31-40,
41-50, 51-60, 60+.

I am relatively new to using formulae on Excel and have tried using the tips
already posted on this site but have not been able to get this to work!

Calculating Age

You can use the DATEDIF to calculate a person's age. For example, the
formula below will calculate the age of a person as of the current date,
where BirthDate is the person's date of birth.

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


I have the TODAY formula in N, and the Dates of birth are in column E. I
just need to know where to add the data and a real step-by-step guide for an
Excel dummy! If someone can help me with this that would be great :-)

date of birth age formula
 

Donna,

If you are getting #VALUE! returned then you probably have the dates entered
as text not XL dates. Re-format Column E as a date and then re-enter the
dates like 31/3/1988, (or 3/31/1988 if you use American style dates).


If you want to use my formuulas then use the amended formulas:

General formula:

=IF(E5="","",FLOOR(DATEDIF(E5,TODAY(),"y"),10)+1&
" - "&CEILING(DATEDIF(E5,TODAY(),"y"),10))

Age Range:

=IF(E5="","",IF(DATEDIF(E5,TODAY(),"y")<16,"Under 16",
IF(DATEDIF(E5,TODAY(),"y")<21,"16 - 20",
IF(DATEDIF(E5,TODAY(),"y")60,"60+",
FLOOR(DATEDIF(E5,TODAY(),"y"),10)+1&" - "&
CEILING(DATEDIF(E5,TODAY(),"y"),10)))))


Columns of Ranges:

=IF(CEILING(DATEDIF(E5,TODAY(),"y"),10)=40,"In this range","")

This will prevent wrong returns when there is not value in Column E.

Post back if you are still having trouble.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Donna" wrote in message
...
Thanks both but where should I enter the formula? Have tried entering
into
the formula bar for a cell, but this just results in #VALUE appearing in
the
cell.

"Mike H" wrote:

I should have pointed out that 60 requires a sllightly different
approach

=SUMPRODUCT(--(E1:E20<""),--(DATEDIF(E1:E20,N1,"y")60))

Mike

"Donna" wrote:

I need to work out ages from dates of birth I have on an Excel
spreadsheet.
Ideally I would like to calculate age ranges, e.g. 16-20, 21-30, 31-40,
41-50, 51-60, 60+.

I am relatively new to using formulae on Excel and have tried using the
tips
already posted on this site but have not been able to get this to work!

Calculating Age

You can use the DATEDIF to calculate a person's age. For example, the
formula below will calculate the age of a person as of the current
date,
where BirthDate is the person's date of birth.

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


I have the TODAY formula in N, and the Dates of birth are in column E.
I
just need to know where to add the data and a real step-by-step guide
for an
Excel dummy! If someone can help me with this that would be great :-)

date of birth age formula
 

You can put the formula anywhere except N1 or E1 - E20.
You will get a value error if your dates of birth in column E aren't
correctly formatted dates

Mike

"Donna" wrote:

Thanks both but where should I enter the formula? Have tried entering into
the formula bar for a cell, but this just results in #VALUE appearing in the
cell.

"Mike H" wrote:

I should have pointed out that 60 requires a sllightly different approach

=SUMPRODUCT(--(E1:E20<""),--(DATEDIF(E1:E20,N1,"y")60))

Mike

"Donna" wrote:

I need to work out ages from dates of birth I have on an Excel spreadsheet.
Ideally I would like to calculate age ranges, e.g. 16-20, 21-30, 31-40,
41-50, 51-60, 60+.

I am relatively new to using formulae on Excel and have tried using the tips
already posted on this site but have not been able to get this to work!

Calculating Age

You can use the DATEDIF to calculate a person's age. For example, the
formula below will calculate the age of a person as of the current date,
where BirthDate is the person's date of birth.

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


I have the TODAY formula in N, and the Dates of birth are in column E. I
just need to know where to add the data and a real step-by-step guide for an
Excel dummy! If someone can help me with this that would be great :-)

date of birth age formula
 

Brilliant, that works! Thanks so much for your patience.

"Sandy Mann" wrote:

Donna,

If you are getting #VALUE! returned then you probably have the dates entered
as text not XL dates. Re-format Column E as a date and then re-enter the
dates like 31/3/1988, (or 3/31/1988 if you use American style dates).


If you want to use my formuulas then use the amended formulas:

General formula:

=IF(E5="","",FLOOR(DATEDIF(E5,TODAY(),"y"),10)+1&
" - "&CEILING(DATEDIF(E5,TODAY(),"y"),10))

Age Range:

=IF(E5="","",IF(DATEDIF(E5,TODAY(),"y")<16,"Under 16",
IF(DATEDIF(E5,TODAY(),"y")<21,"16 - 20",
IF(DATEDIF(E5,TODAY(),"y")60,"60+",
FLOOR(DATEDIF(E5,TODAY(),"y"),10)+1&" - "&
CEILING(DATEDIF(E5,TODAY(),"y"),10)))))


Columns of Ranges:

=IF(CEILING(DATEDIF(E5,TODAY(),"y"),10)=40,"In this range","")

This will prevent wrong returns when there is not value in Column E.

Post back if you are still having trouble.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Donna" wrote in message
...
Thanks both but where should I enter the formula? Have tried entering
into
the formula bar for a cell, but this just results in #VALUE appearing in
the
cell.

"Mike H" wrote:

I should have pointed out that 60 requires a sllightly different
approach

=SUMPRODUCT(--(E1:E20<""),--(DATEDIF(E1:E20,N1,"y")60))

Mike

"Donna" wrote:

I need to work out ages from dates of birth I have on an Excel
spreadsheet.
Ideally I would like to calculate age ranges, e.g. 16-20, 21-30, 31-40,
41-50, 51-60, 60+.

I am relatively new to using formulae on Excel and have tried using the
tips
already posted on this site but have not been able to get this to work!

Calculating Age

You can use the DATEDIF to calculate a person's age. For example, the
formula below will calculate the age of a person as of the current
date,
where BirthDate is the person's date of birth.

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


I have the TODAY formula in N, and the Dates of birth are in column E.
I
just need to know where to add the data and a real step-by-step guide
for an
Excel dummy! If someone can help me with this that would be great :-)

date of birth age formula
 

Glad that it helped. Thanks for the feedback.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Donna" wrote in message
...
Brilliant, that works! Thanks so much for your patience.

"Sandy Mann" wrote:

Donna,

If you are getting #VALUE! returned then you probably have the dates
entered
as text not XL dates. Re-format Column E as a date and then re-enter the
dates like 31/3/1988, (or 3/31/1988 if you use American style dates).


If you want to use my formuulas then use the amended formulas:

General formula:

=IF(E5="","",FLOOR(DATEDIF(E5,TODAY(),"y"),10)+1&
" - "&CEILING(DATEDIF(E5,TODAY(),"y"),10))

Age Range:

=IF(E5="","",IF(DATEDIF(E5,TODAY(),"y")<16,"Under 16",
IF(DATEDIF(E5,TODAY(),"y")<21,"16 - 20",
IF(DATEDIF(E5,TODAY(),"y")60,"60+",
FLOOR(DATEDIF(E5,TODAY(),"y"),10)+1&" - "&
CEILING(DATEDIF(E5,TODAY(),"y"),10)))))


Columns of Ranges:

=IF(CEILING(DATEDIF(E5,TODAY(),"y"),10)=40,"In this range","")

This will prevent wrong returns when there is not value in Column E.

Post back if you are still having trouble.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Donna" wrote in message
...
Thanks both but where should I enter the formula? Have tried entering
into
the formula bar for a cell, but this just results in #VALUE appearing
in
the
cell.

"Mike H" wrote:

I should have pointed out that 60 requires a sllightly different
approach

=SUMPRODUCT(--(E1:E20<""),--(DATEDIF(E1:E20,N1,"y")60))

Mike

"Donna" wrote:

I need to work out ages from dates of birth I have on an Excel
spreadsheet.
Ideally I would like to calculate age ranges, e.g. 16-20, 21-30,
31-40,
41-50, 51-60, 60+.

I am relatively new to using formulae on Excel and have tried using
the
tips
already posted on this site but have not been able to get this to
work!

Calculating Age

You can use the DATEDIF to calculate a person's age. For example,
the
formula below will calculate the age of a person as of the current
date,
where BirthDate is the person's date of birth.

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


I have the TODAY formula in N, and the Dates of birth are in column
E.
I
just need to know where to add the data and a real step-by-step
guide
for an
Excel dummy! If someone can help me with this that would be great
:-)