Forum Discussion

Jithan's avatar
Jithan
Copper Contributor
Jul 18, 2022

Excel time difference error

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 sugg...
excel
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jul 19, 2022

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's avatar
    Jithan
    Copper Contributor
    Jul 20, 2022
    =ISNUMBER(A2) and =ISNUMBER(B2)
    this returns true and if change the values in other cell to 0:50-0:40 results fails
    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Jul 20, 2022

      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.

Resources