Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Round Down a date value (Excel 2003 )

    Why doesn't this formula work? =rounddown(TODAY()-I23)/365.25 I need it to round an age down. For example, if a person's birth date is 12/19/1974, the formula without "rounddown" will calculate an age of 31.87. I want it to have a value of 31.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Round Down a date value (Excel 2003 )

    You can also use
    <code>
    =DATEDIF(I23,TODAY(),"y")
    </code>
    See DATEDIF Function for info about this function that is undocumented within Excel.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Round Down a date value (Excel 2003 )

    <P ID="edit" class=small>(Edited by 79schultz on 01-Nov-06 11:11. Ran into another snag.)</P>I believe I figured it out. Here's what I did.

    [b]=ROUNDDOWN((TODAY()-I14)/365.25,0)[b]

    One other problem I ran into though is that if a child has not yet reached their first birthday, it will show a value of '0'. Is there a way in code or something to have it calculate how many months old the child is?

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

    Re: Round Down a date value (Excel 2003 )

    Try:

    <code>
    =IF(DATEDIF(I23,TODAY(),"y")>0,DATEDIF(I23,TODAY() ,"y")&" years",DATEDIF(I23,TODAY(),"m")&" Months")
    </code>
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Round Down a date value (Excel 2003 )

    Thanks a lot! That's exactly what I needed!

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: Round Down a date value (Excel 2003 )

    Cor blimey Legare

    don't get me started on trying to show someone's current age with Excel.
    I did some work for a hospital once - well more like a baby factory really (according to Excel they produced over two tons of baby per annum) - ok some people might call it a hospital with a speciality maternity unit.
    Anyway, ..so you get a hospital ID assigned to all patients (including babies) and of course you have date of birth and need age etc.
    Simple I thought - Excel no problem!
    ..So for most of us it was OK - we just really want age shown in years.
    For kids, you use different measurements - e.g. mothers will say theirs is "4 months old" or maybe "6 weeks" or "3 days" (anyone born today would be say "five hours old" or maybe just referred to as "newborn".
    Up to the teenage years, children might say "I'm 6 years and 7 months"

    The worst problem of course were the lucky persons born in 1898 etc.
    Never mind the "year 2000" problem, this was the classic "year 1900" problem.
    (Excel not being able to simply handle dates before 01-Jan-1900)

    Good luck in creating a generic formula to handle the lot.

    (I had to use a custom function in the end)

    zeddy

Posting Permissions

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