Mark the Hours in use based on a start and finish time!!! HELP!!

**KattieSpencer** · 12-17-2013, 11:59 AM

I am working on an excel spreadsheet for work and I have to show a chart that shows the time of day a space is in use. Right now I am having to mark all of these by hand which takes me forever because I have a couple thousand lines of information.

I need to mark the hours in use based off the start and finish time. For Example if the start time is 7:45 AM and the end time is 9:00 AM then I need a formula to mark the chart on the 7AM and 8AM spots. If the end time is 9AM I do not want that spot marked because it ends then it is NOT in use then.

I was given the formula =IF(OR(AND($A2>=C$1,$A2<=C$1+30/24/60),AND($B2>=C$1+30/24/60,$B2<=D$1),AND($A2<C$1,$B2>D$1)),1,0) and it sort of works but it does not account for ALL of the 6AM hour or ALL of the 7AM hour...I need the formula to account for 7:00 - 7:59AM and 8:00 to 8:59AM...and I don't know how to fix it.

Attached is an example of the spreadsheet

Excel table.jpg

Is this even possible?!

**Greg M** · 12-18-2013, 11:27 AM

Hi Kattie,

I think part of the problem is that the formula you're using seems to be structured around the use of half-hourly booking periods, while your worksheet is structured around hourly booking periods.

Take a look at the attached workbook and see if it does what you want.

Hope this helps - please let me know how you get on with it.

Regards,

Greg M