formula to calculate age

  • Thread starter Thread starter Ian Rolfe
  • Start date Start date
I

Ian Rolfe

I am required to provide to an authority, ages of members
in our club and once had an excel formula to calculate
this from the previous column in which I had inserted date
of birth.

Regretably, this formula was lost when my last computer
packed up.

Can anyone provide this formula for me?

Would be most grateful for any help

Thanks

Ian Rolfe
 
-----Original Message-----
Assuming you want the years

=DATEDIF(dob,TODAY(),"Y")

--

Regards,

Peo Sjoblom




One further question (if I may)...
Click to expand...
I want the figure "1" to appear in the appropriate column
out of seven column which show age groups. These are:

"Under 21", "21-25", "26-40", "41-50", "51-60", "61-70"
and "Over 70".

I hope you don't mind me 'picking your brains' so much.
I would be most obliged if you could help!!

Best regards,

Ian Rolfe
 
If you want to total the different age ranges there are better ways,
assume dob is in A2:A50, then this formula will return the total
for group 26-40

=SUMPRODUCT(--(DATEDIF(A2:A50,TODAY(),"Y")>=26),--(DATEDIF(A2:A50,TODAY(),"Y
")<=40))

and for under 21

=SUMPRODUCT(--(DATEDIF(A2:A50,TODAY(),"Y")<21))

for over 70

=SUMPRODUCT(--(DATEDIF(A2:A50,TODAY(),"Y")>70))

If you want to use 1 per hit in each age group as in your example

in the Under 21 use

=IF(DATEDIF($A2,TODAY(),"Y")<21,1,"")

copy down

for 21 - 25

=IF(AND(DATEDIF($A2,TODAY(),"Y")>=21,DATEDIF($A2,TODAY(),"Y")<=25),1,"")

for over 70

=IF(DATEDIF($A2,TODAY(),"Y")>70,1,"")

but as I said you can retrieve the total for each age group using sumproduct
 
-----Original Message-----
If you want to total the different age ranges there are better ways,
assume dob is in A2:A50, then this formula will return the total
for group 26-40

=SUMPRODUCT(--(DATEDIF(A2:A50,TODAY(),"Y")>=26),--(DATEDIF (A2:A50,TODAY(),"Y
")<=40))

and for under 21

=SUMPRODUCT(--(DATEDIF(A2:A50,TODAY(),"Y")<21))

for over 70

=SUMPRODUCT(--(DATEDIF(A2:A50,TODAY(),"Y")>70))

If you want to use 1 per hit in each age group as in your example

in the Under 21 use

=IF(DATEDIF($A2,TODAY(),"Y")<21,1,"")

copy down

for 21 - 25

=IF(AND(DATEDIF($A2,TODAY(),"Y")>=21,DATEDIF($A2,TODAY (),"Y")<=25),1,"")

for over 70

=IF(DATEDIF($A2,TODAY(),"Y")>70,1,"")

but as I said you can retrieve the total for each age group using sumproduct

--

Regards,

Peo Sjoblom
Click to expand...
.Peo Sjoblom
Click to expand...
I am most grateful to you for your help (just found your
Click to expand...
answer re age ranges) - Have printed it and will try it
soonest.

Once again, my thanks

Ian V. Rolfe
 
Back
Top