Formula IF function with time range condition

Copperlinks

New Member
Joined
Jul 22, 2010
Messages
5
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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)
 

Copperlinks

New Member
Joined
Jul 22, 2010
Messages
5
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.
 

Copperlinks

New Member
Joined
Jul 22, 2010
Messages
5
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.
 

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129

ADVERTISEMENT

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)
 

Copperlinks

New Member
Joined
Jul 22, 2010
Messages
5
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,675
Messages
5,833,073
Members
430,188
Latest member
EG93

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
Top