Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2003
    Location
    Birmingham, England
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    New Lounger
    Join Date
    Dec 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 [attachment=88152:Birthday example.xls] (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.
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    May 2003
    Location
    Birmingham, England
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  5. #5
    New Lounger
    Join Date
    May 2003
    Location
    Birmingham, England
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    But the DateDif (like XL in general) doe not like to see "negative time"... [The formulas I provided provide for this error....]


    Steve

Posting Permissions

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