Forum Discussion

dfolk13's avatar
dfolk13
Copper Contributor
Nov 27, 2023

Why is my If statement not working?

in my Excel i am utilizing 4 different cells to return the difference in time as an number. However I seem to be getting the wrong output and it only seems to be for a specific time (=2 hours).

 

In my excel I have the following:

 

12:45:00 PM

2:45:00 PM

=B1-A1

=IF(C1<=TIME(1,0,0),1,IF

(C1<=TIME(1,30,0),1.5,IF

(C1<=TIME(2,0,0),2,IF

(C1<=TIME(2,30,0),2.5,IF

(C1<=TIME(3,0,0),3,IF

(C1<=TIME(3,30,0),3.5,IF

(C1<=TIME(4,0,0),4,IF

(C1<=TIME(4,30,0),4.5,IF

(C1<=TIME(5,0,0),5,IF)))))))))

 

 

the output I get is:

 

12:45

14:45

2:00

2.5

 

However, If i change the times but use the same two hour time difference, I get the correct result:

 

14:00:00

16:00:00

=B2-A2

=IF(C2<=TIME(1,0,0),1,IF

(C2<=TIME(1,30,0),1.5,IF

(C2<=TIME(2,0,0),2,IF

(C2<=TIME(2,30,0),2.5,IF

(C2<=TIME(3,0,0),3,IF

(C2<=TIME(3,30,0),3.5,IF

(C2<=TIME(4,0,0),4,IF

(C2<=TIME(4,30,0),4.5,IF

(C2<=TIME(5,0,0),5,IF)))))))))

 

the output I get is:

 

14:00

16:00

2:00

2

 

I have tried to play around with the formatting of the times, but it has not changed my output. It is possibly something to do with the a.m./p.m.?

 

Does anyone know what I am doing wrong, or a better way of doing this?

 

Thanks in advance.

excel

2 Replies

Sort By
  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor
    Nov 27, 2023

    dfolk13 Explanation: Times in Excel are stored as the fraction of one day that they represent. 12:45 PM is (exactly) 0.53125 of a day; 2:45 PM is 0.614583333… of a day. But most real numbers (most numbers that include a fractional or "decimal" portion) cannot be exactly represented in a finite amount of computer memory.

     

    Excel represents (essentially) all numbers as floating point; but Floating-point arithmetic may give inaccurate results in Excel. In this case, the subtraction apparently results in an internal value that is very slightly more than the 0.083333333… which represents two hours, so the equality test (actually, the <= test) that you expected to result in True did not.

     

    Jan's formula does not include any equality tests.  That formula seems to work.  If some edge case(s) were found where it fails, you might just subtract one millisecond (e.g.) from the time difference (column C value) in that formula.

     

Resources