Displaying 1 or 0 if a certain minute value in a cell is displayed

[sobeitchuck]

I am working on project and ultimately have time values listed in a column in hh:mm:ss format. I have been running through numerous forums and cannot find a similar situation that will help me. I want to build an IF statement that meets this criteria:

If minute value in cell A1 is less than 10 minutes or greater than 45 minutes, display "0", otherwise display 1 if the minutes falls in between 10 and 45 minutes.

Here is a sample of what I want display, just cannot get the formula right...

TIMES	TIME IS BETWEEN 10 AND 45 MINUTES (YES/NO)
00:16:00	1
00:09:00	0
00:39:00	1
01:04:00	0
01:31:00	0
00:22:00	1
00:30:00	1

<tbody>
</tbody>

Here is the formula I started with to have 0 displayed if <10minutes and works:

=IF(COUNT(A1),IF(A1<TIME(0,10,0),0,1),"")<time(0,10,0),0,1),"")<time(0,10,0),0,1),"")

I tried adding "OR" to the formula to get the other condition of >45minutes to also display 0 ....I am stuck from here.

=IF(COUNT(A1),IF(A1(OR<TIME(0,10,0),0,1),<TIME(0,45,0),0,1,"")

<time(0,10,0),0,1),l5><time(0,10,0),0,1),(l5<time(0,45,0),0,1,),"") <u=""> THIS DOES NOT WORK


ANY IDEAS?

thanks ahead of time,

sobeitchuck




</time(0,10,0),0,1),(l5<time(0,45,0),0,1,),"")></time(0,10,0),0,1),l5></time(0,10,0),0,1),"")<time(0,10,0),0,1),"")

 

[Aladin Akyurek]

A2:A8 houses time values.

B2, copied down:

=AND(A2*1440>=10,A2*1440<=45)+0

 

[Rick Rothstein]

I am working on project and ultimately have time values listed in a column in hh:mm:ss format. I have been running through numerous forums and cannot find a similar situation that will help me. I want to build an IF statement that meets this criteria:

If minute value in cell A1 is less than 10 minutes or greater than 45 minutes, display "0", otherwise display 1 if the minutes falls in between 10 and 45 minutes.

Here is a sample of what I want display, just cannot get the formula right...

TIMES	TIME IS BETWEEN 10 AND 45 MINUTES (YES/NO)
00:16:00	1
00:09:00	0
00:39:00	1
01:04:00	0
01:31:00	0
00:22:00	1
00:30:00	1

<tbody>
</tbody>

Shouldn't the red highlighted one above be 1, not 0?

Give this formula a try...

=IF(OR(MINUTE(A2)<10,MINUTE(A2)>45),0,1)

 

[Snakehips]

sobeitchuck,

Welcome to MrExcel.

Try....

Excel 2007
	A	B	C	D	E	F
1	TIMES	TIME IS BETWEEN 10 AND 45 MINUTES (YES/NO)
2	00:16:00	1
3	00:09:00	0
4	00:39:00	1
5	01:04:00	0
6	01:31:00	0
7	00:22:00	1
8	00:30:00	1
Sheet1

Cell Formulas
Range	Formula
B2	=IF(AND((A2*24*60)>9,(A2*24*60)<46),1,0)

Hope that helps.

 

[Momentman]

Try

Excel 2010
	A	B
1	TIMES	TIME IS BETWEEN 10 AND 45 MINUTES (YES/NO)
2	00:16:00	1
3	00:09:00	0
4	00:39:00	1
5	01:04:00	0
6	01:31:00	1
7	00:22:00	1
8	00:30:00	1
Sheet1

Cell Formulas
Range	Formula
B2	=IF(AND(MINUTE(A2)>10,MINUTE(A2)<45),1,0)

 

[sobeitchuck]

sobeitchuck,

Welcome to MrExcel.

Try....
Excel 2007

	A	B	C	D	E	F
1	TIMES	TIME IS BETWEEN 10 AND 45 MINUTES (YES/NO)
2	00:16:00	1
3	00:09:00	0
4	00:39:00	1
5	01:04:00	0
6	01:31:00	0
7	00:22:00	1
8	00:30:00	1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =IF(AND((A2*24*60)>9,(A2*24*60)<46),1,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hope that helps.

Snakehips

I got your formula to work, amazing! I also have another condition as well. If there is no time in my column I get a #VALUE error. How would I insert an else quotes to just leave the cell blank?

thanks for super-fast response to my post.

sobeitchuck

 

[Snakehips]

I'm surprised you get an error but assuming you are using excel 2007 + then

=IFERROR(IF(AND((A2*24*60)>9,(A2*24*60)<46),1,0),"")

Glad we could help.

 

[Aladin Akyurek]

Snakehips

I got your formula to work, amazing! ...

Others not? You must be kidding...