Counting time periods

[Haggy76]

I have a bit of a problem: I have start and finish times and dates for operating cases over a 2 year period, I would like to count the number of cases that were ongoing during each 2 hour time period over the day. e.g. if a case starts at 07:00am and finishes at 10:30am it will be included in the 0600-0800, the 0800-1000, and the 1000-1200 time slots. I have solved most of the problem by using multiple COUNTIFS functions, e.g. COUNTIFS(start time,”>08:00:00”,start time,”<10:00:00”,end time,”>08:00:00”,end time,”>10:00:00”……etc. The problem I cannot work out how to solve is when the case starts late on one day and finishes on another e.g. 23:00-02:00. Can you guide me in the right direction please?

[#2]

@Haggy76 

=IF(L6<K6,((L6+1)-K6)*24,(L6-K6)*24)

Hope I was able to help you with this info.

NikolinoDE

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.