True/False Statment Using Four Values

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!

Thanks!

Ian