trouble with time formula

[HoustonSteve]

I am using an if statement as the first field in a row. The statement evaluates whether the elapsed time in another cell (same row) is greater than 30 seconds. The if statement is =IF(H143>TIME(0,0,30),"Y","N"). The value in H143 is 0:00:30 and it has the formula =(G143-F143+(G143<F143)), where G143 is the finish time and F143 is the start time. The value returned in A143 is "Y" when it should be "N" because it is not greater than 30. The formula works correctly for the three rows immediately preceding 143 which also have elapsed time values of 0:00:30 and for the rows succeeding 143 that have values greater than 30. Please help. The formatting for all the times values is the same (h:mm:ss). The only thing I note is that the values for the times (provided as a dump from another database) are left justified and the values for the calculated fields are right justified. Does that indicate that Excel is seeing the original times as text instead of numbers?

 

[c_m_s_jr]

Welcome to the board

Well based on what I can tell the formula is working correctly. Because the results is 30 seconds that is not greater than 30 so it should evaluate to "N". If you want it to be "Y" maye this:

=IF(H143>=TIME(0,0,30),"Y","N")

 

[HoustonSteve]

I've been staring at this for too long...I changed the formula slightly to try to fix it, but =IF(H2>TIME(0,0,30),"Y","N") is still returning "Y" when H2=0:00:30. Shouldn't it return "N" since H2 is not greater than 30?

 

[c_m_s_jr]

You are correct it should return "N". I tried it with a hard coded value in H2, but perhaps is the values from (G2-F2+(G2))? Or whatever is feeding H2. Can you post a sample?

 

[HoustonSteve]

Sure, How do I post a sample? Just cut and paste?

 

[HoustonSteve]

Usable? UNIT DATE CALL TIME Call Proc_Intv DISP ENRT Reflex_Intv
N ME175 5/20/09 21:26:08 0:00:39 21:26:47 21:27:17 0:00:30
Y ME174 5/26/09 14:13:05 0:01:14 14:14:19 14:14:49 0:00:30

Here's the cut and paste version. notice that the formula evaluates the Reflex_Intv as N the first time and the next time as Y. Go figure!

 

[c_m_s_jr]

It looks like it is the results of the formula that is returning under Reflect_Intv. If Reflect_Invt is the results of:

DISP ENRT - Proc_Invt then use the following formula to calculate Reflect_Intv

=SECOND(DISP ENRT - Proc_Invt)

And change your if =IF(H2>30,"Y","N")

I tested and this seemed to work for me.

 

[HoustonSteve]

Unfortunately many of the Reflex_Intv values are more than one minute. Using "seconds" truncates the answer at a maximum of 59 seconds. I do appreciate the help on this....

 

[c_m_s_jr]

Ok, how about using this formula to calculate Reflect_Invt

=ROUND(DISP ENRT - Proc_Invt,9)