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

**KattieSpencer** · 03-25-2014, 12:10 PM

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:
1) If the start time was 7:00 and the end time was 10:45 then the markers would only mark 7am and 10am
2) If the start time was 7:15 and the end time was 10:00 then the markers would only mark 7am and 9am
3) If the start time was 7:30 and the end time was 10:30 then the markers would only mark 7am and 10am
4) If the start time was 7:45 and the end time was 10:15 then the markers would only mark 8am and 9am

I was given the formula and it works to count the whole hour but now I need the formula tweaked and I don't know how...I need the formula to account for quarterly hours...and I don't know how to fix it.

Attached is an example of the spreadsheet of what I currently have and of what I need.
Book1.xlsx

**ChemistB** · 03-25-2014, 01:05 PM

I used this much simpler formula
=IF(AND($A2<=C$1, $B2>=D$1), 1, "-")
Use that formula if,
for the meeting that goes from 7AM to 7:50, do you want a "1" in the 7:45 position?
if yes, then
=IF(AND($A2<=C$1, $B2>=C$1),1,"-")