Trying to plot entire timeline from start and finish times only

[gothemoose]

Hey everyone, long time viewer of the forums but am new to posting...

I have a problem I am finding difficult to solve at the moment. It's time related.

I have separate cells for start times of jobs and finish times of jobs. e.g.
START TIME: 12:30 PM
FINISH TIME: 13:30 PM

My spreadsheet then has headers which shows hourly intervals across 24 hours: 12:00 PM to 12:59 PM, 13:00 PM to 13:59 etc. All across 24 hours for start times and the same for finish times.

I use a formula to check the start time: =IF(AND(START TIME>=12:00PM,START TIME<=12:59PM),"1","0"). This then plots a 1 or a 0 where the time falls into each specific hour across the headers. I do the same for FINISH TIMEs.

When the start time and the finish time are in adjacent (or the same) hours then I can see the 1 or two hour long job plotted next to each other BUT, when the job START TIME is at say 12PM but the FINISH TIME is at 4PM then the hours of 1300-1359, 1400-1459, and 1500-1559 do not get a 1 returned against them. They get a zero which is in fact correct, but I need to show where the hours of the job go across multiple hours.

I really need to show the total time across the whole job for all of the hours that were taken to complete it.

Is there any way of doing this outside of a Macro? I can supply a copy of the spreadsheet if that's easiest! I have however uploaded a screenshot...

I'm really looking forward to your expert opinions!

Cheers,
Jimmy

 

[gothemoose]

So, I fixed it!

It was a simple formula but the logic took me a bit to get around. Thanks for anyone who took it upon themselves to take a look.

Cheers,
Jimmy