A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

what is the formula to change date of birth into age



 
 
Thread Tools Display Modes
  #1  
Old May 16th 07, 12:47 PM posted to microsoft.public.excel.misc
Eddie
external usenet poster
 
Posts: 30
Default what is the formula to change date of birth into age

I am compiling a membership list and need the age because we have an age
limit . Is there a formula to do this using date of birth? Can this be done
without me having to work it out in my head each time!

I am using MSO Excel 2003

Really appreciate any advice.
Thanks.
Ads
  #2  
Old May 16th 07, 01:33 PM posted to microsoft.public.excel.misc
bj
external usenet poster
 
Posts: 1,397
Default what is the formula to change date of birth into age

=datedif(birthdate,today(),"y")

"Eddie" wrote:

> I am compiling a membership list and need the age because we have an age
> limit . Is there a formula to do this using date of birth? Can this be done
> without me having to work it out in my head each time!
>
> I am using MSO Excel 2003
>
> Really appreciate any advice.
> Thanks.

  #3  
Old May 16th 07, 01:33 PM posted to microsoft.public.excel.misc
JLatham
external usenet poster
 
Posts: 3,366
Default what is the formula to change date of birth into age

Here is a 'simple' formula that will report a person's age effective the
current month, i.e. if their birthday is in current month, it will report
that next age even if the day of the month hasn't arrived yet. This 'current
month' condition applies to all formulas below. Assumes their DOB is in A1

=YEAR(NOW())-YEAR(A1)-((MONTH(NOW())<MONTH(A1)))

depending on how your age limit works, here's one that will show age only if
they have passed a specific point (in this formula, they must be 21 or older
for the age to show up, under 21 and "Underage" will show. This is all one
long formula, although it may break here
=IF(YEAR(NOW())-YEAR(A1)-((MONTH(NOW())<MONTH(A1)))>20,YEAR(NOW())-YEAR(A1)-((MONTH(NOW())<MONTH(A1))),"Underage")

or if your limit is the other way around, must NOT have reached a certain
age to join up (must be under 65 to join - of course this lets toddlers join)

=IF(YEAR(NOW())-YEAR(A6)-((MONTH(NOW())<MONTH(A6)))<65,YEAR(NOW())-YEAR(A6)-((MONTH(NOW())<MONTH(A6))),"Too Old")

to filter out the toddlers also, this one allows you to set older than AND
younger than limits:
=IF(YEAR(NOW())-YEAR(A1)-((MONTH(NOW())<MONTH(A1)))>20,IF(YEAR(NOW())-YEAR(A1)-((MONTH(NOW())<MONTH(A1)))<65,YEAR(NOW())-YEAR(A1)-((MONTH(NOW())<MONTH(A1))),"Too Old"),"Too Young")


"Eddie" wrote:

> I am compiling a membership list and need the age because we have an age
> limit . Is there a formula to do this using date of birth? Can this be done
> without me having to work it out in my head each time!
>
> I am using MSO Excel 2003
>
> Really appreciate any advice.
> Thanks.

  #4  
Old May 16th 07, 01:38 PM posted to microsoft.public.excel.misc
Pete_UK
external usenet poster
 
Posts: 8,856
Default what is the formula to change date of birth into age

You could just subtract the dob from TODAY() to get the number of
elapsed days and divide this by 365.25 to get years, but if you want
the answer to be a bit more elaborate then Chip Pearson shows how
he

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

Hope this helps.

Pete

On May 16, 12:47 pm, Eddie > wrote:
> I am compiling a membership list and need the age because we have an age
> limit . Is there a formula to do this using date of birth? Can this be done
> without me having to work it out in my head each time!
>
> I am using MSO Excel 2003
>
> Really appreciate any advice.
> Thanks.



  #5  
Old May 16th 07, 01:47 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default what is the formula to change date of birth into age

Chip Pearson has some very nice notes:
http://www.cpearson.com/excel/datedif.htm

Eddie wrote:
>
> I am compiling a membership list and need the age because we have an age
> limit . Is there a formula to do this using date of birth? Can this be done
> without me having to work it out in my head each time!
>
> I am using MSO Excel 2003
>
> Really appreciate any advice.
> Thanks.


--

Dave Peterson
  #6  
Old May 16th 07, 01:56 PM posted to microsoft.public.excel.misc
Bondi
external usenet poster
 
Posts: 94
Default what is the formula to change date of birth into age

On 16 Maj, 13:47, Eddie > wrote:
> I am compiling a membership list and need the age because we have an age
> limit . Is there a formula to do this using date of birth? Can this be done
> without me having to work it out in my head each time!
>
> I am using MSO Excel 2003
>
> Really appreciate any advice.
> Thanks.


Hi Eddie,

Here are two ways of doing that:

http://www.fontstuff.com/excel/exltut01.htm

and

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

Best regards,
Bondi

  #7  
Old May 16th 07, 02:34 PM posted to microsoft.public.excel.misc
Paul B
external usenet poster
 
Posts: 709
Default what is the formula to change date of birth into age

Eddie, have a look here for some ways to do it
http://www.cpearson.com/excel/datedif.htm#Age
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Eddie" > wrote in message
news
>I am compiling a membership list and need the age because we have an age
> limit . Is there a formula to do this using date of birth? Can this be
> done
> without me having to work it out in my head each time!
>
> I am using MSO Excel 2003
>
> Really appreciate any advice.
> Thanks.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
what is the formula for getting current age from Date of Birth Smudge Excel Discussion (Misc queries) 5 February 17th 10 09:46 PM
how to work out an age from a date of birth Grd Excel Worksheet Functions 4 November 9th 06 05:36 PM
formula to calculate age using birth date and current date lalah Excel Worksheet Functions 2 November 20th 05 10:51 PM
What is the formula to calculate Age when I have Date of Birth? Katiemcgi Excel Worksheet Functions 1 November 1st 04 08:07 PM
What is the formula to calculate Age when I have Date of Birth? Katiemcgi Excel Worksheet Functions 1 November 1st 04 07:15 PM


All times are GMT +1. The time now is 07:44 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.