True/False Statment Using Four Values

iwnixon Posts 3 Registration date Wednesday November 27, 2013 Status Member Last seen April 2, 2014 - Apr 2, 2014 at 11:24 AM
 Blocked Profile - Apr 2, 2014 at 04:11 PM
Hi everyone!

I'm working on a spreadsheet that will drastically reduce the time our staff have to do paperwork, and I'm oh so close but I can't get past the formula. I think I've been staring at it too long and my brain is no longer functioning.

The spreadsheet is a simple Time In/Time Out system. It figures when a particular individual comes in at the beginning of the day and when they leave at the end of the day and then calculates which group activities they were present for based on 30 minute increments. So if an individual comes in at 9a and leaves at 3p, the chart fills in the 12 boxes within those times with "Yes". Anything after is marked with a "No". Pretty simple.

The part I'm getting hung up on is when an individual receives a one on one service, referred to here on out as "FHIO". Once they receive FHIO, they are removed from the group during that time and I need to box to say "No" during that time. I've been tinkering back and forth with the "IF, AND" and the "IF, OR" formulas, but have not been able to come to a solution in which the "No" fills in the appropriate boxes every single time.

So the final example looks like this: An individual arrives at 9a and leaves at 3p, but receives FHIO from 11:00a to 12:30p. The boxes should look like this: 9a-Yes, 9:30a-Yes, 10a-Yes, 10:30a-Yes, 11a-No, 11:30a-No, 12p-No, 12:30p-Yes, 1p-Yes, 1:30-Yes, etc.

Because each 30 minute time box signifies the start of that 30 minutes, if an individual received FHIO from 11a to 12:30p, they would have been out for 11a, 11:30a, 12p, but would have been back in the group at 12:30p.

I apologize for the lengthy post. I just wanted to make everything as clear as I could. If someone could help me out with this, I'd be forever grateful!



1 reply

Blocked Profile
Apr 2, 2014 at 04:11 PM

You do understand that you CAN nest IF/THEN statements. SO, if you need to check different stuff, you can.

Have you ever thought of CASCADING the logic checks down the line to simplify things. So what I mean by that is: write a test sheet, with each cell checking for what you want. When you acheive the outcome, nest them all together in one statement. Do not try to do it all on one line. There is a reason why cells can be hidden!

Let me know if this gives you some help!