Formula IF function with time range condition

New Member
Hello all,
I am attempting to create an If function with time and having difficulty with it.

What I am trying to accomplish is to create a condition between two cells in which a particular range of time is met.

For example:
If time between A1 and B1 fall within the range between 5:15 PM and 5:45 PM, then C1 = 1. So if it's 5:05 PM in A1 and 6:01 PM in B1, value is true. If it's 5:25 in A1 and 5:26 PM in B1, value is still true. If it's 5:46 PM in A1 and 6:01 PM in B1, value is false.

Can this be done?

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sure, you just end up testing both of your input times against the lower bound, then both of them against the upper bound.

assume 5:15 in D1 and 5:45 in E1:

=If(AND(A1>=D1,B1>=D1,A1<=E1,B1<=E1),1,0)

Thank you so much for your assistance, Chris. Unfortunately, the formula is still equaling zero on every test. I'll brainstorm a bit more to see if I can identify the problem, but I suspect it has something to do with the unique format that times and dates present.

Ah, no, I see what the issue is. The value is only true when the time encompasses the entirety of 5:15 and 5:45. I need it to be true even if it's 1 minute within the range between 5:15 and 5:45.

Whoops, you're right, I goofed. Let's try this logic:

Your start time must always be less than 5:45
AND
Your end time must always be greater than 5:00

I think that will work right?

Code:
``=If(AND(A1 < E1,B1 > D1),1,0)``

Brilliant! Thank you Chris, you have no idea how much time you have saved me.

I have a new question on this same subject.

Because date and time are invariably linked, I have to create new boundaries for each specific day of the year. Is there a way for me to set time boundaries, but not specify a date?

Replies
3
Views
136
Replies
2
Views
170
Replies
1
Views
581
Replies
5
Views
154
Replies
3
Views
351

Forum statistics

1,203,489
Messages
6,055,722
Members
444,814
Latest member
AutomateDifficulty

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