#1   Report Post  
Posted to microsoft.public.excel.misc
D G D G is offline
external usenet poster
 
Posts: 1
Default Convert date to Age

I need to convert a date of birth to age. However, I need to base it
on Sept 1, 2011. I need to know how old (years only) that person is
on that date. Does anyone know how to do this. Why is September 1,
2011 so important. It allows me to determine if they are eligible(too
young or too old) to play a sport.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Convert date to Age

Hi,

Am Wed, 7 Sep 2011 12:25:11 -0700 (PDT) schrieb D G:

I need to convert a date of birth to age. However, I need to base it
on Sept 1, 2011. I need to know how old (years only) that person is
on that date. Does anyone know how to do this. Why is September 1,
2011 so important. It allows me to determine if they are eligible(too
young or too old) to play a sport.


date of birth in A1, Sept 1, 2011 in B1 then:
=DATEDIF(A1,B1,"Y")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Convert date to Age

What about
enter this in C1
=($B$1-A1)/365.25

B1 is 1-9-11
A1 is date of birth
change format in column C to number with 1 decimal place.


"D G" wrote in message
...
I need to convert a date of birth to age. However, I need to base it
on Sept 1, 2011. I need to know how old (years only) that person is
on that date. Does anyone know how to do this. Why is September 1,
2011 so important. It allows me to determine if they are eligible(too
young or too old) to play a sport.



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,045
Default Convert date to Age

On Sun, 18 Sep 2011 20:32:20 +1000, "Roger Dodger" wrote:

What about
enter this in C1
=($B$1-A1)/365.25

B1 is 1-9-11
A1 is date of birth
change format in column C to number with 1 decimal place.


According to the OP, a person has to be a certain age as of Sep 1. Using your method, someone who is born on 15 Sep 2000 would show as being 11.0 years old. Since his birthday would not occur for another two weeks, he would be shown as being eligible for sports when he was not.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,045
Default Convert date to Age

On Wed, 7 Sep 2011 12:25:11 -0700 (PDT), D G wrote:

I need to convert a date of birth to age. However, I need to base it
on Sept 1, 2011. I need to know how old (years only) that person is
on that date. Does anyone know how to do this. Why is September 1,
2011 so important. It allows me to determine if they are eligible(too
young or too old) to play a sport.


The DATEDIF function should work properly for your purpose. However, it has not appeared in the HELP context of Excel except in Excel 2000 and, especially since a serious bug has crept into one of the parameters, is thought to be unsupported by MS.

If you would prefer another formula, which does not use DATEDIF, I would suggest:

=YEAR(TargetDate)-YEAR(DOB)-
(DATE(YEAR(TargetDate),MONTH(DOB),DAY(DOB))Target Date)

That merely subtracts the year of birth in DOB from the year in TargetDate (nominally 1-sep-2011), but adds back one if the birthday occurs later in the year than TargetDate.

The only issue of which I am aware, with that algorithm (which would not apply in your case), would be the determination of a leaplings birthday if TargetDate were 28 Feb; the leaplings birthday would not occur until 1 March. Depending on your jurisdiction, that may or may not be appropriate.


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 39
Default Convert date to Age

Roger Dodger,
If you change the resulting cell to two (2) decimal places, in the cell
format, it would be better. Or better yet, use the "rundown function such
as:
" =rounddown(($B$1-A1)/365.25,0) " (w/o quotes) .

Place minimum age in Cell C1 and using:
" =If(rounddown(($B$1-A1)/365.25,0)<$C$1."InEligible",Eligible) " (w/o
quotes) .

--
Rich/rerat
(RRR News) (message rule)
((Previous Text Snipped to Save Bandwidth When Appropriate))


"Roger Dodger" wrote in message
ond.com...
What about
enter this in C1
=($B$1-A1)/365.25

B1 is 1-9-11
A1 is date of birth
change format in column C to number with 1 decimal place.


"D G" wrote in message
...
I need to convert a date of birth to age. However, I need to base it
on Sept 1, 2011. I need to know how old (years only) that person is
on that date. Does anyone know how to do this. Why is September 1,
2011 so important. It allows me to determine if they are eligible(too
young or too old) to play a sport.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I convert US date with 12hr format to European date 24hr Enda K Excel Discussion (Misc queries) 1 November 15th 09 10:59 AM
How to convert Gregorian date into Hijri Date in Excel 2007? Ahmed Excel Discussion (Misc queries) 2 February 6th 09 04:59 PM
Help: How do I convert a text date into a real date format japorms Excel Worksheet Functions 4 August 2nd 06 06:36 PM
to convert a julian date back to regular date Lynn Hanna Excel Worksheet Functions 1 July 26th 06 03:14 PM
Convert a julian gregorian date code into a regular date Robert Excel Worksheet Functions 3 June 13th 06 07:03 PM


All times are GMT +1. The time now is 09:51 AM.

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

About Us

"It's about Microsoft Excel"