Trying to plot entire timeline from start and finish times only

gothemoose

New Member
Joined
Feb 4, 2020
Messages
2
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
Platform
  1. Windows
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
 

Attachments

  • Capture.PNG
    Capture.PNG
    28.3 KB · Views: 3

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

gothemoose

New Member
Joined
Feb 4, 2020
Messages
2
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,638
Messages
5,549,110
Members
410,897
Latest member
Ekrupa25
Top