Excel time difference error

Copper Contributor

I am using a time difference

=A1-B1 

=A2-B2

=A3-B3 and it's value 

=23:50-23:40

=00:50-00:40

=02:40-02:30

But in the middle returns 0:00

And first and third return correct answer  please suggest to correct the result

 

 

 

 

 

 

 

 

 

5 Replies

@Jithan Provided that the second pair of values are correct (numeric) time values it should work. If both are texts, they represent zero, and your result would indeed be zero. You can check by typing =ISNUMBER(A2) and =ISNUMBER(B2). If both return FALSE, you have texts.

@Jithan 

 

Your description is unclear.  I believe you are saying that with what appears to be 00:50 in A2 and what appears to be 00:40 in B2, =A2-B2 results in what appears to be 00:00 in C2, let's say.  Right?

 

Look carefully at the cell formats.  I suspect (*) that A2 and B2 are formatted as Custom m:ss or something similar, whereas C2 is formatted as Custom h:mm.

 

More to the point, the values in A2 and B2 are 0m 50s and 0m 40s.  Thus, A2-B2 results in 0m 10s.

 

You can confirm by formatting C2 as Custom m:ss or h:mm:ss.

 

You can also use =SECOND(A2) and =SECOND(B2).

 

-----

(*) I should add that that is a wild guess on a potentially misunderstanding of the problem.

 

If my guess does not seem right, I suggest that you attach an example Excel file to a response here.  Click the "browse" link near the bottom of the reply window.

 

If the forum does not allow you attach files yet (so I'm told), upload the file to a file-sharing website, and post the download URL in some form (**) in a response here.  I like box.net/files; others like dropbox.com.  You might like onedrive.live.com because it has the same login as this forum.

 

(**) If the forum does not allow you enter URLs, you can spell out the beginning part of the URL; for example, techcommunity dot microsoft dot /t5/excel/excel-time-difference-error/m-p/3575889 .

=ISNUMBER(A2) and =ISNUMBER(B2)
this returns true and if change the values in other cell to 0:50-0:40 results fails

@Jithan Best to share the file so that we can see what's really in it. Replicated your formula in a sheet of my own where I entered the times myself, and it works as expected, though, you could simplify it considerable by combining the three conditions in an AND function, rather that nesting three IF's.