Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Precise Date Difference (2003)

    I have two columns of dates and need to find the number of years, months and days between them as three additional columns - but it needs to be exact.
    For example, 9/1/2007 - 10/1/2008 represents 1 year, 0 months, 1 day. How? Thanks, Andy.

  2. #2
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Precise Date Difference (2003)

    Hi Andrew

    I cannot get this to work over 3 cells, but this does it in 1 =DATEDIF(A1,B1,"y") & "years, " & DATEDIF(A1,B1,"Ym") & "months, " & DATEDIF(A1,B1,"md") & "days", all of our normal 'Einsteins" must be at lunch so you will get a proper answer latter I am sure

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Precise Date Difference (2003)

    Hi Andrew (sorry, see columns F,G and H1 as I was playing around trying to do it other ways in the other cells!!)

    I just had a play about and chopped the formula into 3 and it seems to do what you want, please see attached, I am sure there is something more elegant though

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Precise Date Difference (2003)

    <P ID="edit" class=small>(Edited by Jezza on 10-Jan-08 13:45. To add 3 column comment)</P>Hi there

    How about using the datediff function like so:

    =DATEDIF([startdate],[enddate],"Y") & " Years, " & DATEDIF([startdate],[enddate],"YM") & " Months, " & DATEDIF([startdate],[enddate],"MD") & " Days"

    where [startdate] and [enddate] are your cell references

    In addition to make it go across three columns:

    Assume the start date is in A1 and the end date is in B1

    C1 =DATEDIF(A1,B1,"Y")
    D1 =DATEDIF(A1,B1,"YM")
    E1 =DATEDIF(A1,B1,"md")
    Jerry

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Precise Date Difference (2003)

    Hi Jezza

    Its unusual for me of all people to get in both times about 45 seconds before you <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Precise Date Difference (2003)

    If you want more information on this useful function, which is undocumented in most versions of Excel, see DATEDIF Worksheet Function

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Precise Date Difference (2003)

    Hi Tony

    Thats a great explaination, thanks

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Precise Date Difference (2003)

    .. I agree, and thanks folks. Why 'hide' this function when it would be difficult to find someones precise age without it? Andy.

Posting Permissions

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