IF function to search if cell (in time format) is less than 20 minutes

[andyj2179]

Hi everyone

I need to know how I can create an IF function where it will look at a cell with a time in it (e.g. 00:01:24) and determine if it is less than 20 minutes.

If it is I simply want a 1 to display in my target cell otherwise to remain blank.

Please can someone help me? I know how to do it with normal numbers but with a time format it is confusing me!! arrrgh

Many thanks!

 

[T. Valko]

Hi everyone

I need to know how I can create an IF function where it will look at a cell with a time in it (e.g. 00:01:24) and determine if it is less than 20 minutes.

If it is I simply want a 1 to display in my target cell otherwise to remain blank.

Please can someone help me? I know how to do it with normal numbers but with a time format it is confusing me!! arrrgh

Many thanks!

Try this...

=IF(COUNT(A2),IF(A2 < TIME(0,20,0),1,""),"")<TIME(0,20,0),1,""),"")< html>

 

[VoG]

Try

=IF(A1 < TIME(0,30,0),1,"")

 

[andyj2179]

Lovely thanks!

What does the COUNT function do? (sorry I am an Excel novice

 

[andyj2179]

Thanks!

 

[T. Valko]

Lovely thanks!

What does the COUNT function do? (sorry I am an Excel novice

COUNT is just making sure there is a time value entered in the cell. An empty cell will evaluate as 0 and 0 is less than 20 mins so an empty cell could give you an incorrect result.

 

[BenMiller]

Or =IF((A1>0)*(A1<0.0138888888888889),1,"")

 

[andyj2179]

Thanks everyone for your help! Appreciated

 

[T. Valko]

Thanks everyone for your help! Appreciated

You're welcome!