See the top rated post in this thread. Click here

Results 1 to 9 of 9

Thread: Excel - time between two dates

  1. #1
    Mod / Salary / SM Wanderer's Avatar
    Join Date
    2001-12
    Location
    St. Louis
    Posts
    5,406
    Login to Give a bone
    0

    Default Excel - time between two dates

    I have about 700 records I need to parse here, with a start date and an end date. I'm assuming there must be a function that can tell me the time elapsed between those two dates.

    Does anyone know how to do this?

    Commencement Date Expiration Date
    2000-05-01 2003-04-30
    2001-10-01 2005-09-30
    2002-05-17 2005-09-30
    2005-10-01 2008-09-30
    Melanie Stone
    @MistresDorkness

    Archibus, FMS/FMInteract and AutoCAD Expert (I use BricsCAD, Revit, Tandem, and Planon, too)
    Technical Editor
    not all those who wander are lost

  2. #2
    Mod / Salary / SM Wanderer's Avatar
    Join Date
    2001-12
    Location
    St. Louis
    Posts
    5,406
    Login to Give a bone
    0

    Default Re: Excel - time between two dates

    Need to find an answer? Post the question.

    I kept searching the help file and I think the YEARFRAC function might do it.

    Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date).
    Melanie Stone
    @MistresDorkness

    Archibus, FMS/FMInteract and AutoCAD Expert (I use BricsCAD, Revit, Tandem, and Planon, too)
    Technical Editor
    not all those who wander are lost

  3. #3
    Member
    Join Date
    2010-10
    Location
    Việt Nam
    Posts
    45
    Login to Give a bone
    0

    Default Re: Excel - time between two dates

    What about "-" ?

  4. #4
    Member
    Join Date
    2012-06
    Posts
    2
    Login to Give a bone
    1

    Default Re: Excel - time between two dates

    DAYS360 function - if You want count days

  5. #5
    Mod / Salary / SM Wanderer's Avatar
    Join Date
    2001-12
    Location
    St. Louis
    Posts
    5,406
    Login to Give a bone
    0

    Default Re: Excel - time between two dates

    Quote Originally Posted by Tomasz Radczuk View Post
    DAYS360 function - if You want count days
    Thanks.
    I tried this, and it works as you'd expect... but, I don't see why there are only 360 days in that function's year, and why there isn't a DAYS365 function?
    Melanie Stone
    @MistresDorkness

    Archibus, FMS/FMInteract and AutoCAD Expert (I use BricsCAD, Revit, Tandem, and Planon, too)
    Technical Editor
    not all those who wander are lost

  6. #6
    All AUGI, all the time CADastrophe's Avatar
    Join Date
    2012-01
    Location
    Site of the next CADaclysm
    Posts
    813
    Login to Give a bone
    0

    Default Re: Excel - time between two dates

    Have you tried ketxu's suggestion? If you perform a simple subtraction formula, the result is reported in days, and even accounts for leap year.

    ExcelDateDifferenceDays.JPG

  7. #7
    All AUGI, all the time CADastrophe's Avatar
    Join Date
    2012-01
    Location
    Site of the next CADaclysm
    Posts
    813
    Login to Give a bone
    0

    Default Re: Excel - time between two dates

    Quote Originally Posted by Wanderer View Post
    Thanks.
    I tried this, and it works as you'd expect... but, I don't see why there are only 360 days in that function's year, and why there isn't a DAYS365 function?
    From Microsoft Excel Help:

    Description
    The DAYS360 function returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months.

  8. #8
    Mod / Salary / SM Wanderer's Avatar
    Join Date
    2001-12
    Location
    St. Louis
    Posts
    5,406
    Login to Give a bone
    0

    Default Re: Excel - time between two dates

    Quote Originally Posted by CADastrophe View Post
    From Microsoft Excel Help:
    Okay, the explanations I read only had the first sentence of that. The rest gives some helpful perspective.
    Melanie Stone
    @MistresDorkness

    Archibus, FMS/FMInteract and AutoCAD Expert (I use BricsCAD, Revit, Tandem, and Planon, too)
    Technical Editor
    not all those who wander are lost

  9. #9
    Mod / Salary / SM Wanderer's Avatar
    Join Date
    2001-12
    Location
    St. Louis
    Posts
    5,406
    Login to Give a bone
    0

    Default Re: Excel - time between two dates

    Quote Originally Posted by CADastrophe View Post
    Have you tried ketxu's suggestion? If you perform a simple subtraction formula, the result is reported in days, and even accounts for leap year.

    ExcelDateDifferenceDays.JPG
    Um, no, I saw it and didn't understand it and meant to come back and look again later... (ha, if I had a dollar for every time).
    That is nice and easy, isn't it? Pretty simple and I don't know why I didn't think of it first. Always trying to complicate things, I guess.

    But, in this case, I think I'll stick with YEARFRAC, as that's the unit in which I'm reporting the end number.
    Melanie Stone
    @MistresDorkness

    Archibus, FMS/FMInteract and AutoCAD Expert (I use BricsCAD, Revit, Tandem, and Planon, too)
    Technical Editor
    not all those who wander are lost

Similar Threads

  1. AutoCad and Excel VBA Run time error
    By mklindquist370941 in forum VBA/COM Interop
    Replies: 1
    Last Post: 2013-11-08, 03:46 PM
  2. Invisible dates?
    By Dave Jones in forum Revit Architecture - General
    Replies: 9
    Last Post: 2010-10-21, 07:24 PM
  3. File dates
    By pcsyoga in forum AutoLISP
    Replies: 3
    Last Post: 2004-06-23, 11:43 AM

Posting Permissions

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