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

sobeitchuck

New Member
Joined
Sep 14, 2014
Messages
2
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...

TIMESTIME IS BETWEEN 10 AND 45 MINUTES (YES/NO)
00:16:001
00:09:000
00:39:001
01:04:000
01:31:000
00:22:001
00:30:001

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

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),"")
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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...

TIMESTIME IS BETWEEN 10 AND 45 MINUTES (YES/NO)
00:16:001
00:09:000
00:39:001
01:04:000
01:31:00
0
00:22:001
00:30:001

<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)
 
Upvote 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
Sheet1
Cell Formulas
RangeFormula
B2=IF(AND((A2*24*60)>9,(A2*24*60)<46),1,0)


Hope that helps.
 
Upvote 0
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)
 
Upvote 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

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

Worksheet Formulas
CellFormula
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top