• age calculation, XL2003 SP3

    Author
    Topic
    #466752

    I’ve read the previous thread, plus John Walkenbach’s excellent ‘XL2003 Bible’, but am still getting wrong answers!
    In column A I have birthdates, formatted dd/mm/yyyy and named range ‘birthdate’
    In column B I have the formula
    =(today()-birthdate)/365.25 etc, copied down. This is formatted as a number, 3 decimal places.
    Today’s date as at writing is 18 Feb 2010 and the computer clock shows that date.
    With a selection of birthdates like 18/02/yyyy I’d expect to find reported values of exactly an integral number of years.
    From a choice of ‘birthyears’, I only get that for birthdate = 18/02/1910, which returns 100.000 (and good for 6 decimal places, in fact) and 18/02/2000, returning 20.000
    All others show small supplements to a whole number of years.
    The same situation occurs using the DATEDIF function (which I want to avoid anyway as it can’t handle future birthdates, ie negative ages)
    I really would like someone born 10 years ago today to show as precisely 10 years old, not 10.001(369…)
    What’s the trick, please?

    I suspect that it might be a question of how many 29 Februaries intervene? If so, what’s the fix?

    And why won’t it handle birthdates before the year 1900? (but that’s less important.)

    Thanks!

    John

    Viewing 4 reply threads
    Author
    Replies
    • #1209912

      The fractional portion is being calculated (probably) due to a combination of the time component that goes along with every date and the fractional portion of days per year. If you’re interested in whole numbers only, you can play with cell formats to display the nonfractional portion of the number only, or use the TRUNC function to remove the fractional part entirely. I’ve attached an example (I’m using Excel 2007 and I hope it translates properly to older versions).

      In answer to your second question, Excel converts all dates to a serial number starting at 1900 (or 1904). Earlier dates are problematic. I added an example that deals with that problem, too–not a pretty, unfortunately.

    • #1209920

      Thanks v much, Peter.
      Concerning the ‘time component of TODAY(), it seems always to be zero (just like it sort of says it should be). It’s the NOW() function which includes ‘time elapsed so far’ within ‘TODAY’.
      The TRUNC function or applying a suitable format works, of course, but don’t do what I want, which is to count down (or up) **grin** to say an impending birthday and it would be really good if the person became exactly 24, say, on the unique and exact date corresponding to his 24th birthday!
      This is not a show-stopper but I’m annoyed with myself about having been unable, so far, to bottom it out.
      Thanks again.
      John

    • #1210009

      What do you mean by datedif can not handle future birthdates? To calculate an age you are using their date of birth compared to some future date, since the future birthdate is larger than the DOB.

      You only would get negative values if you want the age of someone who was NOT born yet. If you need the age of someone before they were born, you could just presume it is 0 or reverse the values in the datedif and take the negative of it…

      =IF(A1<TODAY(),DATEDIF(A1,TODAY(),"y"),0)
      or
      =IF(A1<TODAY(),DATEDIF(A1,TODAY(),"y"),-DATEDIF(TODAY(),A1,"y"))
      or even
      =IF(A1<TODAY(),DATEDIF(A1,TODAY(),"y"),"Not Born Yet")

      Steve

    • #1210023

      Steve,
      >>>>What do you mean by datedif can not handle future birthdates?
      Merely that it reports an error rather than returns a minus number.
      “Age = -3 years” means, to me, “will be born in 3 years time from now”, rather more meaningful than the #NUM error returned by DATEDIF.
      John

    • #1210027

      But the DateDif (like XL in general) doe not like to see “negative time”… [The formulas I provided provide for this error….]

      Steve

    Viewing 4 reply threads
    Reply To: age calculation, XL2003 SP3

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: