+ Reply to Thread
Results 1 to 5 of 5

Calculate time difference after 24hrs

  1. #1
    Registered User
    Join Date
    06-03-2009
    Location
    N.Ireland
    MS-Off Ver
    Excel 2003
    Posts
    76

    Calculate time difference after 24hrs

    I am having some trouble calculating a time difference between 2 times. I have started by using two columns that hold dates and i have a simple formula to calculate the difference in days between each date, this is fine. I know need 2 count the difference in hours between eg 10 Jul 09 @ 23:00hrs and 12 Jul 09 @ 13:30hrs. I am currently using the following formula but it doesnt count the hours if the time difference if its over two days:

    Please Login or Register  to view this content.
    Can anyone help?
    Last edited by PRodgers; 07-16-2009 at 04:57 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Calculate time difference after 24hrs

    Assuming U3 is end datetime and H3 is start time then

    Result: =U3-H3
    Format Cell to [hh]:mm

    If you want only complete hours then

    Result: =FLOOR(U3-H3,"01:00")
    Format Cell to [hh]

  3. #3
    Registered User
    Join Date
    06-03-2009
    Location
    N.Ireland
    MS-Off Ver
    Excel 2003
    Posts
    76

    Re: Calculate time difference after 24hrs

    Quote Originally Posted by DonkeyOte View Post
    Assuming U3 is end datetime and H3 is start time then

    Result: =U3-H3
    Format Cell to [hh]:mm

    If you want only complete hours then

    Result: =FLOOR(U3-H3,"01:00")
    Format Cell to [hh]


    Hey thanks for the reply, i have tried using a difference formula, i have three columns at the end of my spreadsheet, the first column (DR) calculates the difference in days between the two dates, as below:

    Please Login or Register  to view this content.
    The second column (DS) then calculates the difference in the two times, as below:

    =U3-H3 +IF(H3>U3,2)

    This formula works fine if the time is <= 24 hrs (even if the date goes on into the next day eg start time 22:00hrs (16/7/2008), end time 01:30hrs
    (17/7/2008). The problem i have is if the start date is (16/7/2009) and the end date is (18/7/2009) the formula doesnt count the extra day (24 hrs) onto the total.

    I tried this as a work around in the third column (DT) which add 24hrs to the value in column (DS), i have to manual check the value in (DR) to check if it equals "2" and if it does add the formula in column (DT) for that specific row.

    =DS3+(24/24)*1

    I tried using the above formula with an IF statement to check the value of column (DR) but it doesnt work:

    Please Login or Register  to view this content.
    PS sorry for the laboured explanation but im just trying to explain what im doing

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Calculate time difference after 24hrs

    Hey thanks for the reply, i have tried using a difference formula
    OK... why not actually try what is suggested ?

    It would make a great deal more sense to post a sample file.

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Calculate time difference after 24hrs

    Quote Originally Posted by PRodgers View Post
    I know need 2 count the difference in hours between eg 10 Jul 09 @ 23:00hrs and 12 Jul 09 @ 13:30hrs.
    So what's the answer you require for the above example?

    If you want the answer to be "1 day(s) 14:30" in a single cell then try this formula

    =S3-G3-(H3>U3)&" day(s) "&TEXT(U3-H3+1,"h:mm")

    If you want days and hours in separate cells you can just split that into

    =S3-G3-(H3>U3)

    for days

    =MOD(U3-H3,1)

    for hours (formatted as time)

    or, lastly, to get total hours

    =S3+U3-G3-H3

    format as [h]:mm

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1