# Calculate time difference after 24hrs

1. ## 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?

2. ## 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. ## Re: Calculate time difference after 24hrs

Originally Posted by DonkeyOte
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. ## 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. ## Re: Calculate time difference after 24hrs

Originally Posted by PRodgers
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

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

#### 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