Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel formula to save time........ (2000)

    Hi,
    I have a rather mishapen excel spreadsheet where some date have been entered and some not.
    To fill those spaces, I basically need to find out the length of a time period between 2 dates, ie 14/5/2004 and 5/2013. The first figure always has the day, month and year. The last is the expiry time, and will always need to be displayed as the last date in that month (although this isn't on the database), so in this case it would be 31/5/2013.
    I need to split the timeline into months and years..............not too sure how too though.

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

    Re: Excel formula to save time........ (2000)

    With the first date in A1 and the second date in B1, you can use this formula (courtesy of Chip Pearson):

    =DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & " months, " & DATEDIF(A1,B1,"md") & " days"

    If you want the individual parts, use

    =DATEDIF(A1,B1,"y")
    =DATEDIF(A1,B1,"ym")

    to get the years and months, respectively.

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

    Re: Excel formula to save time........ (2000)

    Adding to HANS' formula, if B1 has the settlement date entered as 5/2013 (which is the first of the month) and you want to use the last day of the month, then you can change HANS' formula to:

    <pre>=DATEDIF(A1,DATE(YEAR(B1),MONTH(B1)+1,0),"y ") & " years, " & DATEDIF(A1,DATE(YEAR(B1),MONTH(B1)+1,0),"ym") & " months, " & DATEDIF(A1,DATE(YEAR(B1),MONTH(B1)+1,0),"md") & " days"
    </pre>

    Legare Coleman

Posting Permissions

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