Forum Discussion
If Statement Problem
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!
9 Replies
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.
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.
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.
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?
