Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Calculate Day Difference From 2 Dates

  1. #1
    Join Date
    30th July 2008
    Posts
    4

    Calculate Day Difference From 2 Dates

    Hi Guys

    is anyone aware of a formula/macro that can calculate (in days, hours and minutes) how much time has elapsed between two date and time stamps?

    For example [30/07/2008 00:30] and [01/08/2008 01:45], so in days hours and minutes it would look somewhat like this: 02:01:15

    Cheers

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    Re: Calculating Time Elapsed Between Two Date And Time Stamps

    Hi

    Something wrong with just:

    =B1-A1

    if end is in B1 and start in A1?

    Richard

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    30th July 2008
    Posts
    4

    Re: Calculating Time Elapsed Between Two Date And Time Stamps

    sorry i thought it would be that easy too...

    however probably my example wasnt the best. That formula is ok if the dates are within a few days of each other, but if they are over a month apart, it wont calculate correctly and we need to calculate in days for our reporting. so we will need to calculate:

    [30/07/2008 12:30:00 AM] and [28/09/2008 1:45:00 AM] The formula will only count up to 30 days and we require it to go above that. Is that possible?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    Re: Calculating Time Elapsed Between Two Date And Time Stamps

    I don't understand I'm afraid - the formula will do for any 2 date/time values used in Excel.

    The formatting of the result is another issue altogether

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    30th July 2008
    Posts
    4

    Re: Calculating Time Elapsed Between Two Date And Time Stamps

    oh. sorry it may be a formatting issue.... if so then i am not sure how to make it count in just days not months

    [30/07/2008 12:30:00 AM] [1/09/2008 12:30:00 AM] with the above formula would be: 02:00:00 as the day value will only go up to 31

    for that example i need it to say: 33:00:00.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,322

    Re: Calculating Time Elapsed Between Two Date And Time Stamps

    As you've learned, Excel considers d or dd to be the day of the month, and so won't exceed 31. You can do this, but the result will be text:

    =INT(B1-A1) & TEXT(B1-A1, ":hh:mm")
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    Re: Calculating Time Elapsed Between Two Date And Time Stamps

    Do you mean


    Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
     
     =Day(B1)-Day(A1) 


  8. #8
    Join Date
    30th July 2008
    Posts
    4

    Re: Calculate Day Difference From 2 Dates

    =INT(B1-A1) & TEXT(B1-A1, ":hh:mm")

    the result worked perfectly.
    thank you to everyone else for their help too!!

    cheers

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Calculate difference between two dates in sharepoint
    By Dave Hawley in forum Office 2007 Migration and Application Compatibility
    Replies: 0
    Last Post: May 10th, 2009, 03:32
  2. Calculate Difference In Days From 2 Dates
    By mcandoy in forum EXCEL HELP
    Replies: 1
    Last Post: August 1st, 2008, 03:31
  3. Replies: 2
    Last Post: February 2nd, 2008, 09:17
  4. Calculate Time difference between two Dates
    By BVic in forum EXCEL HELP
    Replies: 4
    Last Post: August 23rd, 2006, 10:08
  5. Calculate the difference between dates
    By krithika in forum EXCEL HELP
    Replies: 2
    Last Post: February 10th, 2005, 20:48

Bookmarks

Posting Permissions

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