Using excel to calculate hours open without double counting overlaps

L

lizgraham90

New Member
Joined
May 23, 2019
Messages
6
Hi, I hope someone will be able to help me please.

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 unique hours to count.

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 should be 5 hours. However the formula =MAX(0,IF(A2<>A1,C2-B2,C2-MAX(B2,AGGREGATE(14,6,C$1:C1/(A$1:A1=A2),1))))*24 is wrongly giving me 11 hours.

Any help would be great. The formula above works for some combinations of bookings but not all as detailed below.

DateTime Booking Commenced Time Booking EndedHours to count
04/04/201810:0011:001
04/04/201814:0015:004
04/04/201814:0016:001
04/04/201819:0020:004
04/04/201819:0021:001
05/04/201810:0012:002
05/04/201812:0016:004
05/04/201817:0018:002
05/04/201819:0020:002
05/04/201820:0021:001
06/04/201809:0013:004
06/04/201810:0012:000
06/04/201814:0017:004
06/04/201814:0018:001
<colgroup><col width="144" style="width: 108pt;"> <col width="144" style="width: 108pt;" span="2"> <col width="144" style="width: 108pt;"> <tbody> </tbody>
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Try this VBA solution. It doesn't deal with overlaps but it is a start
Code: 
Sub Hours_To_Count()


Application.ScreenUpdating = False


Dim X As Long, L As Long, J As Long


X = ActiveSheet.UsedRange.Rows.Count


For L = 2 To X
    
    J = DateDiff("h", ActiveSheet.Range("B" & L), ActiveSheet.Range("C" & L))
 
    If J <> 0 Then
 
        ActiveSheet.Range("D" & L).Value2 = J
 
    End If


Next L


Application.ScreenUpdating = True


End Sub
 
Last edited:
Upvote 0
Thanks for this MoshiM but unfortunately I am not familiar with VBA. Could this be done as a formula in excel instead?
 
Upvote 0
Hi,

I changed "Hours to Count" to represent unique hours, it works for the data here, but fails for the triple overlap as in overlaps in 3 consecutive rows, let me know if that is a constraint:

ABCDEFG
1DateTime Booking CommencedTime Booking EndedHours to count
24/4/201810:0011:0014/4/20185
34/4/201814:0015:001
44/4/201814:0016:001
54/4/201819:0020:001
64/4/201819:0021:001
75/4/201810:0012:002
85/4/201812:0016:004
95/4/201817:0018:001
105/4/201819:0020:001
115/4/201820:0021:001
126/4/20189:0013:004
136/4/201810:0012:000
146/4/201814:0017:003
156/4/201814:0018:001

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
G2=SUMIF(A2:A15,$F$2,D2:D15)
D2=IF(A2=A1,IF(AND(B2=B1,C2>=C1),C2-B2-D1/24,IF(AND(B2>B1,C2<=C1),0,C2-B2))*24,(C2-B2)*24)
D3=IF(A3=A2,IF(AND(B3=B2,C3>=C2),C3-B3-D2/24,IF(AND(B3>B2,C3<=C2),0,C3-B3))*24,(C3-B3)*24)
D4=IF(A4=A3,IF(AND(B4=B3,C4>=C3),C4-B4-D3/24,IF(AND(B4>B3,C4<=C3),0,C4-B4))*24,(C4-B4)*24)
D5=IF(A5=A4,IF(AND(B5=B4,C5>=C4),C5-B5-D4/24,IF(AND(B5>B4,C5<=C4),0,C5-B5))*24,(C5-B5)*24)
D6=IF(A6=A5,IF(AND(B6=B5,C6>=C5),C6-B6-D5/24,IF(AND(B6>B5,C6<=C5),0,C6-B6))*24,(C6-B6)*24)
D7=IF(A7=A6,IF(AND(B7=B6,C7>=C6),C7-B7-D6/24,IF(AND(B7>B6,C7<=C6),0,C7-B7))*24,(C7-B7)*24)
D8=IF(A8=A7,IF(AND(B8=B7,C8>=C7),C8-B8-D7/24,IF(AND(B8>B7,C8<=C7),0,C8-B8))*24,(C8-B8)*24)
D9=IF(A9=A8,IF(AND(B9=B8,C9>=C8),C9-B9-D8/24,IF(AND(B9>B8,C9<=C8),0,C9-B9))*24,(C9-B9)*24)
D10=IF(A10=A9,IF(AND(B10=B9,C10>=C9),C10-B10-D9/24,IF(AND(B10>B9,C10<=C9),0,C10-B10))*24,(C10-B10)*24)
D11=IF(A11=A10,IF(AND(B11=B10,C11>=C10),C11-B11-D10/24,IF(AND(B11>B10,C11<=C10),0,C11-B11))*24,(C11-B11)*24)
D12=IF(A12=A11,IF(AND(B12=B11,C12>=C11),C12-B12-D11/24,IF(AND(B12>B11,C12<=C11),0,C12-B12))*24,(C12-B12)*24)
D13=IF(A13=A12,IF(AND(B13=B12,C13>=C12),C13-B13-D12/24,IF(AND(B13>B12,C13<=C12),0,C13-B13))*24,(C13-B13)*24)
D14=IF(A14=A13,IF(AND(B14=B13,C14>=C13),C14-B14-D13/24,IF(AND(B14>B13,C14<=C13),0,C14-B14))*24,(C14-B14)*24)
D15=IF(A15=A14,IF(AND(B15=B14,C15>=C14),C15-B15-D14/24,IF(AND(B15>B14,C15<=C14),0,C15-B15))*24,(C15-B15)*24)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Thanks Aryatect but yes unfortunately this is a constraint.

The formula fails in the example below:

DateTime Booking Commenced Time Booking EndedHours to count
09/04/201808:0013:005
09/04/201810:0011:000
09/04/201810:0011:001
09/04/201815:0016:001
09/04/201816:0017:001
09/04/201818:0019:001
09/04/201818:0021:002
09/04/201819:0020:000
09/04/201819:0021:002
09/04/201819:0021:000
09/04/201819:0022:003
<colgroup><col width="144" style="width: 108pt;"> <col width="144" style="width: 108pt;" span="2"> <col width="144" style="width: 108pt;"> <tbody> </tbody>

Any further help would be appreciated

Thanks
Liz
 
Upvote 0
lizgraham90 said:
Thanks Aryatect but yes unfortunately this is a constraint.

The formula fails in the example below:

DateTime Booking Commenced Time Booking EndedHours to count
09/04/201808:0013:005
09/04/201810:0011:000
09/04/201810:0011:001
09/04/201815:0016:001
09/04/201816:0017:001
09/04/201818:0019:001
09/04/201818:0021:002
09/04/201819:0020:000
09/04/201819:0021:002
09/04/201819:0021:000
09/04/201819:0022:003

<tbody>
</tbody>

Any further help would be appreciated

Thanks
Liz
Click to expand...

Ohkk, will try to work it out and post back if I get somewhere :)
 
Upvote 0
How about


Excel 2013/2016
ABCDEF
1DateTime Booking CommencedTime Booking EndedHours to count
204/04/201810:0011:00104/04/20185
304/04/201814:0015:00105/04/20189
404/04/201814:0016:00106/04/20188
504/04/201819:0020:00109/04/201811
604/04/201819:0021:001
705/04/201810:0012:002
805/04/201812:0016:004
905/04/201817:0018:001
1005/04/201819:0020:001
1105/04/201820:0021:001
1206/04/201809:0013:004
1306/04/201810:0012:000
1406/04/201814:0017:003
1506/04/201814:0018:001
1609/04/201808:0013:005
1709/04/201810:0011:000
1809/04/201810:0011:000
1909/04/201815:0016:001
2009/04/201816:0017:001
2109/04/201818:0019:001
2209/04/201818:0021:002
2309/04/201819:0020:000
2409/04/201819:0021:000
2509/04/201819:0021:000
2609/04/201819:0022:001
Sheet1
Cell Formulas
RangeFormula
D2=(C2-B2)
D3{=IF(A3<>A2,C3-B3,IF(C3<=MAX(IF($A$2:$A2=A3,C$2:C2)),0,IF(B3)))}
F2=SUMIF($A$2:$A$26,E2,$D$2:$D$26)*24
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
The formula you showed in post 1 that, i think, i suggested on another thread, returned different values for me. Are you using the correct ranges?


A​
B​
C​
D​
1​
Date​
Time Booking Commenced​
Time Booking Ended​
Hours to count​
2​
04/04/2018​
10:00​
11:00​
1​
3​
04/04/2018​
14:00​
15:00​
1​
4​
04/04/2018​
14:00​
16:00​
1​
5​
04/04/2018​
19:00
​
20:00​
1​
6​
04/04/2018​
19:00
​
21:00​
1​
7​
05/04/2018​
10:00​
12:00​
2​
8​
05/04/2018​
12:00​
16:00​
4​
9​
05/04/2018​
17:00​
18:00​
1​
10​
05/04/2018​
19:00​
20:00​
1​
11​
05/04/2018​
20:00​
21:00​
1​
12​
06/04/2018​
09:00
​
13:00​
4​
13​
06/04/2018​
10:00
​
12:00​
0​
14​
06/04/2018​
14:00​
17:00​
3​
15​
06/04/2018​
14:00​
18:00​
1​

<tbody>
</tbody>


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

M.
 
Upvote 0
Marcelo Branco said:
The formula you showed in post 1 that, i think, i suggested on another thread, returned different values for me. Are you using the correct ranges?



A​

B​

C​

D​

1​

Date​

Time Booking Commenced​

Time Booking Ended​

Hours to count​

2​

04/04/2018​

10:00​

11:00​

1​

3​

04/04/2018​

14:00​

15:00​

1​

4​

04/04/2018​

14:00​

16:00​

1​

5​

04/04/2018​

19:00
​

20:00​

1​

6​

04/04/2018​

19:00
​

21:00​

1​

7​

05/04/2018​

10:00​

12:00​

2​

8​

05/04/2018​

12:00​

16:00​

4​

9​

05/04/2018​

17:00​

18:00​

1​

10​

05/04/2018​

19:00​

20:00​

1​

11​

05/04/2018​

20:00​

21:00​

1​

12​

06/04/2018​

09:00
​

13:00​

4​

13​

06/04/2018​

10:00
​

12:00​

0​

14​

06/04/2018​

14:00​

17:00​

3​

15​

06/04/2018​

14:00​

18:00​

1​

<tbody>
</tbody>


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

M.
Click to expand...

I have no idea why this is giving me the wrong values. My table is exactly the same as yours. Do I need to use Ctrl+Shift+Enter with this formula like Fluff mentioned in his post?

Sorry know that I am probably being silly but I greatly appreciate everyone's help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

AOB
  • Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
270
AOB
AOB
S
  • Question
Using IFS to calculate a task due by date but needing to remove weekends
Replies
3
Views
80
awoohaw
awoohaw
A
  • Question
Add a specified number of working hours to a date & time, given working day start, end and break times and holidays
Replies
2
Views
257
AquaBuoy
A
V
  • Solved
Add hour in time with condition
2
Replies
10
Views
653
Vishaal
V
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
311
awoohaw
awoohaw

Forum statistics

Threads
1,214,985
Messages
6,122,607
Members
449,090
Latest member
vivek chauhan

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top