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.

[TABLE="width: 432"]
<colgroup><col width="144" style="width: 108pt;"> <col width="144" style="width: 108pt;" span="2"> <col width="144" style="width: 108pt;"> <tbody>[TR]
[TD="width: 144, bgcolor: #BDD7EE"]Date[/TD]
[TD="width: 144, bgcolor: #BDD7EE"]Time Booking Commenced [/TD]
[TD="width: 144, bgcolor: #BDD7EE"]Time Booking Ended[/TD]
[TD="width: 144, bgcolor: #BDD7EE"]Hours to count[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]04/04/2018[/TD]
[TD="bgcolor: transparent"]10:00[/TD]
[TD="bgcolor: transparent"]11:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]04/04/2018[/TD]
[TD="bgcolor: transparent"]14:00[/TD]
[TD="bgcolor: transparent"]15:00[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]04/04/2018[/TD]
[TD="bgcolor: transparent"]14:00[/TD]
[TD="bgcolor: transparent"]16:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]04/04/2018[/TD]
[TD="bgcolor: transparent"]19:00[/TD]
[TD="bgcolor: transparent"]20:00[/TD]
[TD="bgcolor: transparent, align: right"]4[/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, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]05/04/2018[/TD]
[TD="bgcolor: transparent"]10:00[/TD]
[TD="bgcolor: transparent"]12:00[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]05/04/2018[/TD]
[TD="bgcolor: transparent"]12:00[/TD]
[TD="bgcolor: transparent"]16:00[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]05/04/2018[/TD]
[TD="bgcolor: transparent"]17:00[/TD]
[TD="bgcolor: transparent"]18:00[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]05/04/2018[/TD]
[TD="bgcolor: transparent"]19:00[/TD]
[TD="bgcolor: transparent"]20:00[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]05/04/2018[/TD]
[TD="bgcolor: transparent"]20:00[/TD]
[TD="bgcolor: transparent"]21:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]06/04/2018[/TD]
[TD="bgcolor: transparent"]09:00[/TD]
[TD="bgcolor: transparent"]13:00[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]06/04/2018[/TD]
[TD="bgcolor: transparent"]10:00[/TD]
[TD="bgcolor: transparent"]12:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]06/04/2018[/TD]
[TD="bgcolor: transparent"]14:00[/TD]
[TD="bgcolor: transparent"]17:00[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]06/04/2018[/TD]
[TD="bgcolor: transparent"]14:00[/TD]
[TD="bgcolor: transparent"]18:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #BDD7EE"]Date[/TD]
[TD="bgcolor: #BDD7EE"]Time Booking Commenced[/TD]
[TD="bgcolor: #BDD7EE"]Time Booking Ended[/TD]
[TD="bgcolor: #BDD7EE"]Hours to count[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4/4/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]11:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4/4/2018[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4/4/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]14:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]15:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4/4/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]14:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]16:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4/4/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]19:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]20:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4/4/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]19:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]21:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5/4/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12:00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5/4/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]16:00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5/4/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]17:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]18:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5/4/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]19:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]20:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5/4/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]20:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]21:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6/4/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]13:00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6/4/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6/4/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]14:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]17:00[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6/4/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]14:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]18:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=SUMIF(A2:A15,$F$2,D2:D15)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D5[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D6[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D7[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D8[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D9[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D10[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D11[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D12[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D13[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D14[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D15[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks Aryatect but yes unfortunately this is a constraint.

The formula fails in the example below:

[TABLE="width: 432"]
<colgroup><col width="144" style="width: 108pt;"> <col width="144" style="width: 108pt;" span="2"> <col width="144" style="width: 108pt;"> <tbody>[TR]
[TD="width: 144, bgcolor: #BDD7EE"]Date[/TD]
[TD="width: 144, bgcolor: #BDD7EE"]Time Booking Commenced [/TD]
[TD="width: 144, bgcolor: #BDD7EE"]Time Booking Ended[/TD]
[TD="width: 144, bgcolor: #BDD7EE"]Hours to count[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]08:00[/TD]
[TD="bgcolor: transparent"]13:00[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]10:00[/TD]
[TD="bgcolor: transparent"]11:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]10:00[/TD]
[TD="bgcolor: transparent"]11:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]15:00[/TD]
[TD="bgcolor: transparent"]16:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]16:00[/TD]
[TD="bgcolor: transparent"]17:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]18:00[/TD]
[TD="bgcolor: transparent"]19:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]18:00[/TD]
[TD="bgcolor: transparent"]21:00[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]19:00[/TD]
[TD="bgcolor: transparent"]20:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]19:00[/TD]
[TD="bgcolor: transparent"]21:00[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]19:00[/TD]
[TD="bgcolor: transparent"]21:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]19:00[/TD]
[TD="bgcolor: transparent"]22:00[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 432"]
<tbody>[TR]
[TD="width: 144, bgcolor: #BDD7EE"]Date[/TD]
[TD="width: 144, bgcolor: #BDD7EE"]Time Booking Commenced [/TD]
[TD="width: 144, bgcolor: #BDD7EE"]Time Booking Ended[/TD]
[TD="width: 144, bgcolor: #BDD7EE"]Hours to count[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]08:00[/TD]
[TD="bgcolor: transparent"]13:00[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]10:00[/TD]
[TD="bgcolor: transparent"]11:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]10:00[/TD]
[TD="bgcolor: transparent"]11:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]15:00[/TD]
[TD="bgcolor: transparent"]16:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]16:00[/TD]
[TD="bgcolor: transparent"]17:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]18:00[/TD]
[TD="bgcolor: transparent"]19:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]18:00[/TD]
[TD="bgcolor: transparent"]21:00[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]19:00[/TD]
[TD="bgcolor: transparent"]20:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]19:00[/TD]
[TD="bgcolor: transparent"]21:00[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]19:00[/TD]
[TD="bgcolor: transparent"]21:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]09/04/2018[/TD]
[TD="bgcolor: transparent"]19:00[/TD]
[TD="bgcolor: transparent"]22:00[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

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?


[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]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Date​
[/TD]
[TD]
Time Booking Commenced​
[/TD]
[TD]
Time Booking Ended​
[/TD]
[TD]
Hours to count​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
04/04/2018​
[/TD]
[TD]
10:00​
[/TD]
[TD]
11:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
04/04/2018​
[/TD]
[TD]
14:00​
[/TD]
[TD]
15:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
04/04/2018​
[/TD]
[TD]
14:00​
[/TD]
[TD]
16:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
04/04/2018​
[/TD]
[TD]
19:00
​
[/TD]
[TD]
20:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
04/04/2018​
[/TD]
[TD]
19:00
​
[/TD]
[TD]
21:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
05/04/2018​
[/TD]
[TD]
10:00​
[/TD]
[TD]
12:00​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
05/04/2018​
[/TD]
[TD]
12:00​
[/TD]
[TD]
16:00​
[/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
05/04/2018​
[/TD]
[TD]
17:00​
[/TD]
[TD]
18:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
05/04/2018​
[/TD]
[TD]
19:00​
[/TD]
[TD]
20:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
05/04/2018​
[/TD]
[TD]
20:00​
[/TD]
[TD]
21:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
06/04/2018​
[/TD]
[TD]
09:00
​
[/TD]
[TD]
13:00​
[/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD]
06/04/2018​
[/TD]
[TD]
10:00
​
[/TD]
[TD]
12:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD]
06/04/2018​
[/TD]
[TD]
14:00​
[/TD]
[TD]
17:00​
[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD]
06/04/2018​
[/TD]
[TD]
14:00​
[/TD]
[TD]
18:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
</tbody>[/TABLE]


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?


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
A​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
B​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
C​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
D​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
1​
[/TD]
[TD]
Date​
[/TD]
[TD]
Time Booking Commenced​
[/TD]
[TD]
Time Booking Ended​
[/TD]
[TD]
Hours to count​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
2​
[/TD]
[TD]
04/04/2018​
[/TD]
[TD]
10:00​
[/TD]
[TD]
11:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
3​
[/TD]
[TD]
04/04/2018​
[/TD]
[TD]
14:00​
[/TD]
[TD]
15:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
4​
[/TD]
[TD]
04/04/2018​
[/TD]
[TD]
14:00​
[/TD]
[TD]
16:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
5​
[/TD]
[TD]
04/04/2018​
[/TD]
[TD]
19:00
​
[/TD]
[TD]
20:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
6​
[/TD]
[TD]
04/04/2018​
[/TD]
[TD]
19:00
​
[/TD]
[TD]
21:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
7​
[/TD]
[TD]
05/04/2018​
[/TD]
[TD]
10:00​
[/TD]
[TD]
12:00​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
8​
[/TD]
[TD]
05/04/2018​
[/TD]
[TD]
12:00​
[/TD]
[TD]
16:00​
[/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
9​
[/TD]
[TD]
05/04/2018​
[/TD]
[TD]
17:00​
[/TD]
[TD]
18:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
10​
[/TD]
[TD]
05/04/2018​
[/TD]
[TD]
19:00​
[/TD]
[TD]
20:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
11​
[/TD]
[TD]
05/04/2018​
[/TD]
[TD]
20:00​
[/TD]
[TD]
21:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
12​
[/TD]
[TD]
06/04/2018​
[/TD]
[TD]
09:00
​
[/TD]
[TD]
13:00​
[/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
13​
[/TD]
[TD]
06/04/2018​
[/TD]
[TD]
10:00
​
[/TD]
[TD]
12:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
14​
[/TD]
[TD]
06/04/2018​
[/TD]
[TD]
14:00​
[/TD]
[TD]
17:00​
[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
15​
[/TD]
[TD]
06/04/2018​
[/TD]
[TD]
14:00​
[/TD]
[TD]
18:00​
[/TD]
[TD]
1​
[/TD]
[/TR]
</tbody>[/TABLE]


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

B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
57
Bernie Galve
B
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
248
dshtesy
D
A
  • Solved
Count entry by time
Replies
3
Views
168
admvlad
A
M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
269
shift-del
shift-del
AOB
  • Question Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
671
AOB
AOB

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

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