Logical test if time between values

[Darren Guess]

Good Afternoon

I am looking to carry out an 'if' statement to test if a time value is between 2 values and return True or False. I have read around 50 posts today, but cannot find one that deals with values beyond the hours in a day e.g. 24:00.

The closest I have found (and am displaying the modified formula) =IF(AND(HOUR(K1525)>=10,HOUR(K1525)<=100),TRUE,FALSE), but this always returns a false regardless of whether the hours are within the range or not. My values can be anything up to 1000:00 hours. I expect this is because the 'HOUR' function in the formula is to do with hours in the day e.g. 24:00, or should this work for any time value?

My times are formatted in to [HH]:mm formats, so have also tried converting to text etc. and trying to formula from this but again no luck.

Thanks in advance
Darren

 

[mikerickson]

Can you give us some examples of where your formula goes wrong.

If you are using full Excel serial time (that includes date information as well as time information), you could use
=AND(A1 < K125, K125 < B1) to test if K125 is after A1 but before B1.

 

[Darren Guess]

I think it goes wrong because I don't have two columns of data to compare and I am not looking at dates only [HH]:mm formats. I have a list of tasks with estimated hours against them, I then have a total of these tasks, which is the value I want to do the logical test on. In the below example, I want to return 'True' if it is >= to 10 hrs, or <= 100 hrs (which 47:45 would be).

[TABLE="class: grid, width: 469"]
<TBODY>[TR]
[TD]Task Ref</SPAN></SPAN>
[/TD]
[TD]Description</SPAN></SPAN>
[/TD]
[TD]Hrs</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]52C1002</SPAN></SPAN>
[/TD]
[TD]R2 DOOR HANDLE INSTALL FOR MAINTENANCE</SPAN></SPAN>
[/TD]
[TD]00:45</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]52C1005</SPAN></SPAN>
[/TD]
[TD]L4 DOOR HANDLE INSTALL FOR MAINTENANCE</SPAN></SPAN>
[/TD]
[TD]00:45</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]52C1006</SPAN></SPAN>
[/TD]
[TD]R4 DOOR HANDLE INSTALL FOR MAINTENANCE</SPAN></SPAN>
[/TD]
[TD]00:45</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]52C1901</SPAN></SPAN>
[/TD]
[TD]L2 DOOR DEACTIVATE AFTER MAINTENANCE</SPAN></SPAN>
[/TD]
[TD]00:45</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]52C1902</SPAN></SPAN>
[/TD]
[TD]R2 DOOR DEACTIVATE AFTER MAINTENANCE</SPAN></SPAN>
[/TD]
[TD]00:45</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]52C1903</SPAN></SPAN>
[/TD]
[TD]L3 DOOR CLOSE AFTER MAINTENANCE</SPAN></SPAN>
[/TD]
[TD]00:45</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]52C1904</SPAN></SPAN>
[/TD]
[TD]R3 DOOR CLOSE AFTER MAINTENANCE</SPAN></SPAN>
[/TD]
[TD]00:45</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]52C1905</SPAN></SPAN>
[/TD]
[TD]L4 DOOR DEACTIVATE AFTER MAINTENANCE</SPAN></SPAN>
[/TD]
[TD]00:45</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]52C1906</SPAN></SPAN>
[/TD]
[TD]R4 DOOR DEACTIVATE AFTER MAINTENANCE</SPAN></SPAN>
[/TD]
[TD]00:45</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]25T5901</SPAN></SPAN>
[/TD]
[TD]MAIN DECK CARGO LOADING SYS STA 860-1200 - INSTALL</SPAN></SPAN>
[/TD]
[TD]40:00</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]53C2959</SPAN></SPAN>
[/TD]
[TD]LEFT HORIZONTAL STAB BLADE SEALS - INSTALL</SPAN></SPAN>
[/TD]
[TD]00:20</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]53C2961</SPAN></SPAN>
[/TD]
[TD]RIGHT HORIZONTAL STAB BLADE SEALS - INSTALL</SPAN></SPAN>
[/TD]
[TD]00:20</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]C4-DUE-ON-AC</SPAN></SPAN>
[/TD]
[TD]C4 CHECK DUE ON AIRCRAFT</SPAN></SPAN>
[/TD]
[TD]00:10</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]TASKS-72-MTH</SPAN></SPAN>
[/TD]
[TD]B757 72 MONTH MAINTENANCE REQUIREMENTS</SPAN></SPAN>
[/TD]
[TD]00:10</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total</SPAN></SPAN>
[/TD]
[TD]47:45</SPAN></SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


the example you gave is similar to one I had tried modifying earlier, but again this was using two columns of data, where it was looking at two dates and comparing if this was between these values.

 

[mikerickson]

Try =AND(TIME(10,0,0) <=K1525, K1525<=TIME(100,0,0))

 

[Darren Guess]

Hi

This is still returning False on all occasions. I copied your formula in to my spread sheet and it returns false. With my actual data this was correct as the value was 321:16. I then changed K1525<=TIME(100,0,0)) to K1525<=TIME(400,0,0)), expecting to see true as the value falls between 10 and 400, but it still returned false.

Is there something else I need to do to format the time?

 

[mikerickson]

Are those excel serial times or strings what does =ISNUMBER(A1) show (adjust the cell reference)

 

[Darren Guess]

I've adjusted this for K1525 =ISNUMBER(K1525) and it returns 'True'

Would it make any difference that cell K1525 has the formula =SUM(K1526:L1534) to return the value?