Results 1 to 6 of 6

Thread: XL Date Formula

  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    XL Date Formula

    Does anyone know how to use XL's date formula to determine the exact age of someone. As an example:

    Birthdate: 01/01/00
    Today's Date: 05/31/01
    Calculated Age = 1.4166

    I'm trying to utilize the date formula for benefit calculations.

  2. #2
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: XL Date Formula

    Hi
    This is crude but it may work for you

    Assume Birthday in a1
    Calc Age in a2 with

    =(today()-a1)/365

    You will need to format A2 a number, say two decimal places.
    There are far more sophisticated calculations about.

    Cheers
    Geof

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL Date Formula

    If the birth date is in A1, then:

    <pre>=(Today() - A1) / 365.25
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL Date Formula

    You can also use =DATEDIF(A1,today(),"d")/365.25 <img src=/S/alien.gif border=0 alt=alien width=14 height=15>

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL Date Formula

    I don't like the approximation here- and it can give "not quite" the right answer. But your post prompted me to come up with:
    =DATEDIF(A1,NOW(),"Y")

    But the help on DATEDIF says that it's included only for compatibility with Excel. So there must be another function somewhere?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL Date Formula

    hi,
    I use this one to give an accurate age. (in the example A6 is the birthdate)

    =DATEDIF(A6,NOW(),"y") & "y, "& DATEDIF(A6,NOW(),"ym")&"m, "& DATEDIF(A6,NOW(),"md") & "d"

    This displays as: 43y, 0m, 16d

    HTH

    Regards
    Farside

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •