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

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