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?!
Bookmarks