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

sobeitchuck

New Member
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:

Code:
``=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.

Code:
``=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?

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),"")

Last edited:

Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
A2:A8 houses time values.

B2, copied down:

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

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)

sobeitchuck,

Welcome to MrExcel.

Try....

Excel 2007
ABCDEF
1TIMESTIME IS BETWEEN 10 AND 45 MINUTES (YES/NO)
200:16:001
300:09:000
400:39:001
501:04:000
601:31:000
700:22:001
800:30:001
Sheet1
Cell Formulas
RangeFormula
B2=IF(AND((A2*24*60)>9,(A2*24*60)<46),1,0)

Hope that helps.

Try

Excel 2010
AB
1TIMESTIME IS BETWEEN 10 AND 45 MINUTES (YES/NO)
200:16:001
300:09:000
400:39:001
501:04:000
601:31:001
700:22:001
800:30:001
Sheet1
Cell Formulas
RangeFormula
B2=IF(AND(MINUTE(A2)>10,MINUTE(A2)<45),1,0)

sobeitchuck,

Welcome to MrExcel.

Try....
Excel 2007
ABCDEF
1TIMESTIME IS BETWEEN 10 AND 45 MINUTES (YES/NO)
200:16:001
300:09:000
400:39:001
501:04:000
601:31:000
700:22:001
800:30:001

</tbody>
Sheet1

Worksheet Formulas
CellFormula
B2=IF(AND((A2*24*60)>9,(A2*24*60)<46),1,0)

</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

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),"")

Snakehips

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

Others not? You must be kidding...

Replies
3
Views
264
Replies
4
Views
549
Replies
5
Views
449
Replies
1
Views
422
Replies
3
Views
176

1,221,064
Messages
6,157,718
Members
451,434
Latest member
VanDookie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back