1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel - Time Between Dates

Discussion in 'Business Applications' started by Donar, Jan 8, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Donar

    Donar Thread Starter

    Joined:
    Jan 8, 2010
    Messages:
    4
    Excel 2007
    I want to calculate the time (in years) between 2 dates ... so that a 27 month span would appear as 2.25 years.
    Using the formula " =year(A1)-year(A2) " yields an answer in whole years. I want an answer accurate to 1 (or 2) decimal places.
    Defining the cell with the above formula in it, as a "Number" with 1 (or 2) decimal places does not do it,
    Any suggestions ?
    Thanks
     
  2. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Try this

    =(YEAR(A1)-YEAR(A2))+((MONTH(A1)-MONTH(A2))/12)

    and use "Number" with 1 (or 2) decimal places
     
  3. Donar

    Donar Thread Starter

    Joined:
    Jan 8, 2010
    Messages:
    4
    Your formula worked ... gave me just what I needed.
    Thank you
    Donar
     
  4. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    You are very welcome.
     
  5. edhicks

    edhicks

    Joined:
    Jun 30, 2008
    Messages:
    248
    Another approach
    b1=1/1/02 format as date
    b2=3/1/04 format as date
    b3 =(b2-b1)/365.24 format as number 2 decimal places
     
  6. Donar

    Donar Thread Starter

    Joined:
    Jan 8, 2010
    Messages:
    4
    My dates were 12/31/09 and 10/29/04 .... looking for answer (time between dates) in "Years".
    .... using the "=(a2-a1)/365.24" formula .... I get an answer of "40073.17" ..... correct answer is "5.17"

    Did I miss something ?
    Thanks for the input
     
  7. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Something not right there Donar

    I have just used your formula =(a2-a1)/365.24 with the relevant days in A1 and A2 and I get 5.17

    Open a blank worksheet and put the dates in and type the formula out again and see what you get (not forgetting to format the result cell with Value and 2 decimal places)
     
  8. Donar

    Donar Thread Starter

    Joined:
    Jan 8, 2010
    Messages:
    4
    The Villan & edhicks
    My apolgies, i re-entered the values and the short formula ... and it does yield the correct answer. I don't know why my first attempt failed, I tried to re-create the "error" and could not.
    Thank you both
    Donar
     
  9. edhicks

    edhicks

    Joined:
    Jun 30, 2008
    Messages:
    248
    Donar
    For what it's worth,
    I was able to recreate the answer of "40073.17" with the formula
    =a2-a1/365.24
    Glad everything is working and WELCOME to this forum. Isn't it awesome.
     
  10. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Forgot the brackets he he :)

    Only kidding, but brackets do make a big big difference.

    BODMAS springs to mind
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/892312

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice