Checking to see if time difference is 1 hour using if function

[Prevost]

Hi There. I want to use an if function to determine whether or not the time difference between 2 cells is not equal to 1 hour. I tried

=if((A2-A1)=0.0416666666666667, 0,1)

but it does not work all the time. For example, 16:00-15:00 is not 0.0416666666666667. I converted the time difference of one hour to a number. Is there a way to check if the time difference is 1:00?

I also tried creating a column that displays the difference between the times in the 2 cells and used

if(B2=time(1,0,0),1,0)

and it doesn't work all the time because again, the time difference of 1 hour is not always 0.0416666666666667, some times it is 0.0416666666666666.

Any help would be great!

 

[jasonb75]

It could be one of the anomalies of floating point precision.

See if this gives a more accurate result

=--(text(A2-A1,"h:mm:ss.000")="1:00:00.000")

 

[Phydeaux]

=if(OR((A2-A1)=0.0416666666666667,(A2-A1)=0.0416666666666666), 0,1)

or

=if(AND((A2-A1)<0.0416666666666668,(A2-A1)>0.0416666666666665), 0,1)

 

[Prevost]

I used the Round function and then wrote vba to enter a 1 in any cells that did not equal 0.04167. I used vba so that I could Ctrl+Down to each row that was incorrect. Excel just plows through all the rows if there is a formula in it, even if the value is "" (which is technically a value i guess).

Thanks.