Using excel to calculate hours open without double counting overlaps

[lizgraham90]

Hi, I hope someone will be able to help me please.

Ok I am trying to work out how long a community centre is open each day. In Column A I have the date, in Column B I have the start time of a booking, in Column C I have the end time of the booking, Column D is the duration of the booking.

I want to calculate total hours each day that the community centre is open, eliminating any double or triple counting if there is any overlap in bookings.

For example in the table below the total hours open on 04/04/2018 would be 6.5 hours.

Date	Time booking from	Time booking to	Duration of booking
04/04/2018	14:00	16:30	2.5
04/04/2018	18:00	19:00	1
04/04/2018	19:00	21:00	2
04/04/2018	18:00	22:00	4
06/04/2018	18:00	22:00	4
07/04/2018	09:00	11:00	2
07/04/2018	13:00	16:00	3
07/04/2018	14:00	17:00	3
09/04/2018	09:00	14:30	5.5
09/04/2018	12:00	13:30	1.5
09/04/2018	14:00	15:30	1.5

<colgroup><col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <col width="126" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4608;"> <col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3949;"> <col width="132" style="width: 99pt; mso-width-source: userset; mso-width-alt: 4827;"> <tbody> </tbody>

 

[Marcelo Branco]

Welcome to Mr Excel forum

Assuming data grouped by days and Time Booking from in ascending order on each day maybe this

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Date​	Time booking from​	Time booking to​	Duration of booking​	Helper​		Day​	Result​
2​	04/04/2018​	14:00​	16:30​	2,5​	2,5​		04/04/2018​	6,5​
3​	04/04/2018​	18:00​	19:00​	1​	1​		06/04/2018​	4​
4​	04/04/2018​	19:00​	21:00​	2​	2​		07/04/2018​	6​
5​	04/04/2018​	18:00​	22:00​	4​	1​		09/04/2018​	6,5​
6​	06/04/2018​	18:00​	22:00​	4​	4​
7​	07/04/2018​	09:00​	11:00​	2​	2​
8​	07/04/2018​	13:00​	16:00​	3​	3​
9​	07/04/2018​	14:00​	17:00​	3​	1​
10​	09/04/2018​	09:00​	14:30​	5,5​	5,5​
11​	09/04/2018​	12:00​	13:30​	1,5​	0​
12​	09/04/2018​	14:00​	15:30​	1,5​	1​
13​

<tbody>
</tbody>

Helper Column
Formula in E2 copied down
=MAX(0,IF(A2<>A1,C2-B2,C2-MAX(B2,AGGREGATE(14,6,C$1:C1/(A$1:A1=A2),1))))*24

Days in column G

Formula in H2 copied down
=SUMIF(A$2:A$12,G2,E$2:E$12)

Hope this helps

M.

 

[lizgraham90]

Hi Marcelo Branco thank you so very much for your help. Your formula is working perfectly for me.

I greatly appreciate your prompt reply, this problem had been pickling my head for hours!

Thanks again
lizgraham90

 

[Marcelo Branco]

You are welcome. Glad to help

M.