Excel time difference, including hundredth of a se

Question

i want to calculate time difference using excel, between two cells containing numbers in format hh:mm:ss,00. a simple subtraction doesn't work. Anyone can help me? thanks

Answer 1

If the two cells contain valid time values then a subtraction should work assuming you are subtracting the smaller value from the larger one (Excel doesn't like negative time values unless you use 1904 Date System).

What formula did you try? What result did you get, what result do you expect?

Answer 2

You can use the If function so that you don't get the negative error. Do

Instead of =TEXT(A1-B1)

You can do:

=IF(A1>B1,A1-B1,B

Answer 3

After a long search, found this out.

=VALUE(LEFT(B4,8)-LEFT(A4,8))+VALUE((RIGHT(B4,3)/86400000)-(RIGHT(A4,3)/86400000))

Answer 4

I know this question is pretty much dead now but I was looking at this earlier while trying to make a good formula to calculate hours worked in an office. Like by hour and minute. What I came up with was this:

=HOUR([@[Time Ended]])-HOUR([@[Time Started]])+(MINUTE([@[Time Ended]])-MINUTE([@[Time Started]]))/60

This was the formula I used in my hours column in my table to calculate the hours worked from two times, Time Started and Time ended. Notice I had to do the hour and minutes seperately, this is because there are two different functions for these. There is also a function for the seconds in a time, if you include seconds in the inputed time. Adding this into the formula above wouldn't be too difficult.