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.

[TABLE="width: 353"]
<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>[TR]
[TD="width: 104, bgcolor: transparent"]Date[/TD]
[TD="width: 126, bgcolor: transparent"]Time booking from[/TD]
[TD="width: 108, bgcolor: transparent"]Time booking to[/TD]
[TD="width: 132, bgcolor: transparent"]Duration of booking[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]04/04/2018[/TD]
[TD="bgcolor: transparent"]14:00[/TD]
[TD="bgcolor: transparent"]16:30[/TD]
[TD="bgcolor: transparent"]2.5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]04/04/2018[/TD]
[TD="bgcolor: transparent"]18:00[/TD]
[TD="bgcolor: transparent"]19:00[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]04/04/2018[/TD]
[TD="bgcolor: transparent"]19:00[/TD]
[TD="bgcolor: transparent"]21:00[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]04/04/2018[/TD]
[TD="bgcolor: transparent"]18:00[/TD]
[TD="bgcolor: transparent"]22:00[/TD]
[TD="bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]06/04/2018[/TD]
[TD="bgcolor: transparent"]18:00[/TD]
[TD="bgcolor: transparent"]22:00[/TD]
[TD="bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]07/04/2018[/TD]
[TD="bgcolor: transparent"]09:00[/TD]
[TD="bgcolor: transparent"]11:00[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]07/04/2018[/TD]
[TD="bgcolor: transparent"]13:00[/TD]
[TD="bgcolor: transparent"]16:00[/TD]
[TD="bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]07/04/2018[/TD]
[TD="bgcolor: transparent"]14:00[/TD]
[TD="bgcolor: transparent"]17:00[/TD]
[TD="bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]09:00[/TD]
[TD="bgcolor: transparent"]14:30[/TD]
[TD="bgcolor: transparent"]5.5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]12:00[/TD]
[TD="bgcolor: transparent"]13:30[/TD]
[TD="bgcolor: transparent"]1.5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]14:00[/TD]
[TD="bgcolor: transparent"]15:30[/TD]
[TD="bgcolor: transparent"]1.5[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Welcome to Mr Excel forum

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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Date​

[/TD]
[TD]

Time booking from​

[/TD]
[TD]

Time booking to​

[/TD]
[TD]

Duration of booking​

[/TD]
[TD]

Helper​

[/TD]
[TD][/TD]
[TD]

Day​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

04/04/2018​

[/TD]
[TD]

14:00​

[/TD]
[TD]

16:30​

[/TD]
[TD]

2,5​

[/TD]
[TD]

2,5​

[/TD]
[TD][/TD]
[TD]

04/04/2018​

[/TD]
[TD]

6,5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

04/04/2018​

[/TD]
[TD]

18:00​

[/TD]
[TD]

19:00​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

06/04/2018​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

04/04/2018​

[/TD]
[TD]

19:00​

[/TD]
[TD]

21:00​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

07/04/2018​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

04/04/2018​

[/TD]
[TD]

18:00​

[/TD]
[TD]

22:00​

[/TD]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

09/04/2018​

[/TD]
[TD]

6,5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

06/04/2018​

[/TD]
[TD]

18:00​

[/TD]
[TD]

22:00​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

07/04/2018​

[/TD]
[TD]

09:00​

[/TD]
[TD]

11:00​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

07/04/2018​

[/TD]
[TD]

13:00​

[/TD]
[TD]

16:00​

[/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

07/04/2018​

[/TD]
[TD]

14:00​

[/TD]
[TD]

17:00​

[/TD]
[TD]

3​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

09/04/2018​

[/TD]
[TD]

09:00​

[/TD]
[TD]

14:30​

[/TD]
[TD]

5,5​

[/TD]
[TD]

5,5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

09/04/2018​

[/TD]
[TD]

12:00​

[/TD]
[TD]

13:30​

[/TD]
[TD]

1,5​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

09/04/2018​

[/TD]
[TD]

14:00​

[/TD]
[TD]

15:30​

[/TD]
[TD]

1,5​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.