If Statement Problem

[Tito1995]

Hello,

 I am having some difficulty trying to figure out the right Excel formula and I was hoping to get some help from somebody. Basically, I have a Start Time column, an End Time column and then I have a column for each hour block during a 24 hour day. For example, 8:00 am - 9:00 am, 9:00 am - 10:00 am, etc. My objective is to determine on which hour blocks would each start time/end time range fall within. For example, in the first row I have start time = 8:35:16 AM and end time = 9:45:58 AM. On this case the start time/end time range will fall within the 8:00 am - 9:00 am and 9:00 am - 10:00 am. 

I was trying to use this formula: =IF(AND(S1>=MIN(Q2:R2),S1<=MAX(Q2:R2)),"1","0") , but I get 0 for everything and I should be getting 1 for the two hour blocks mentioned before. I am assuming that the hour block cells are not written properly perhaps. Any thoughts? Thank you in advance!

[#2]

@Tito1995 

With the introduction of the dash in the cell those times in Row 1 are treated as text.  Excel is comparing text against times (decimals) in Q and R.

I think you have two options here:

-Keep the setup as is and use extraction formulas to pull out the times from the text strings in row 1

-List only the first hour in the time ranges then add the second via custom cell formatting

e.g.

[#3]

@Patrick2788 I followed the second suggestion that you gave me but I keep getting all "0" for some reason. Excel is not linking the start time/end time range to any hour block cell. Am I missing something in the formula maybe? 

[#4]

@Tito1995 

Here is a better look at the custom cell formatting code:

h:mm AM/PM " - 1:00AM"

[#5]

@Patrick2788 I did that but for the first appointment which last from 8:35:16 AM to 9:45:58 AM, I keep getting "0" at the 8:00:00 AM - 9:00:00 AM hour block in cell AA2. I should be getting "1" for that one since the appointment takes place during that hour block and the next one (9:00:00 AM - 10:00:00 AM).

[#6]

@Tito1995 

If you're able to upload a workbook (Even the first couple rows and the formula), I can have a look.

[#7]

@Patrick2788 I am not able to upload a workbook here. It just allows me to send pictures and links.

But the formula I am using is this one: =IF(AND(S1>=MIN($Q2:$R2),S1<=MAX($Q2:$R2)),"1","0"). 

[#8]

@Tito1995 

Hello,

I am not a fan of "AND" personally. I prefer to tackle my 'If's' one at a time.

Try this; =IF((S$1>MIN($Q2:$R2)),IF((S$1<MAX($Q2:$R2)),"1","0"),"0")

But, I'm not sure of the outcome you wanted for Q3:R3? The block is less than an hour, so there is no "1". If you do want the "1" in the 6 am hour, then you should check the start time, then use the length of time to determine whether or not you compare the end time to the current hour value, or the next hour value. 

[#9]

@Tito1995 

Since you indicated that you are unable to upload your workbook, I tried to replicate your situation from the screenshots. The attached workbook does what you ask for, but, to make things easy, I introduced a row that numbers each column, representing the beginning of each hour. Below that, I created text strings like "9AM-10AM". and so on, but these are for cosmetic purposes only. Obviously, you would have to change the structure of your own sheet a little bit and re-write the formulae so that the ranges fit yours.

Row 3 and 4 use dynamic array formulae (new Excel). Rows 8 and 9 has formulae that achieve the same but using old-Excel functionality.

[#10]

@Tito1995 

The issue could be with Q:R.  @Riny_van_Eekelen 's workbook is worth a look.