date of birth age formula

D

Donna

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 :)
 
M

Mike H

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
 
M

Mike H

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

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

Mike
 
S

Sandy Mann

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(DATEDIF(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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
D

Donna

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.
 
S

Sandy Mann

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
M

Mike H

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
 
S

Sandy Mann

Glad that it helped. Thanks for the feedback.

--

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top