Formula to calculate days with gaps and overlap

[garythebadger]

Picture a table:
Column A is people's names, with some duplication.
Column B is the date they started a project, no blanks.
Column C is the date they finished a project, no blanks.
They log a new start and end date every time they work on their project.
I want to calculate the total days each person spent on all their projects,
knowing there are gaps and overlap of the dates for any particular name.
Gap, for example: They worked a week, took a week off, worked another week.
Overlap, for example: The dates they log between two rows may overlap if they're working on two projects at the same time. I do not want to include this overlap.

I'm thinking: Calculate the diff between earliest and the latest date for each name,
then subtract the gaps and subtract the overlaps for that name?

 

[Luke M]

To make sure I undersand, data could look like this?

In which case, days for Tom should be 7+8+5 = 20
and Jerry would be 7+16 = 23?

 

[garythebadger]

Yes, Luke, exactly!

 

[Luke M]

Is your data sorted by chance?

 

[garythebadger]

I own the report, so I can sort it any way it needs to be.

 

[Luke M]

Had to use an UDF (user defined function), but I think this produces the correct result. You could copy the code to your own workbook, or transfer your data to this workbook.

 

[garythebadger]

Luke, I just sent you a private message, I cannot open your file, company security blocks it.

 

[Luke M]

Hi Gary,

Here's the code I used. With the new attached file, right-click on the sheet tab. Select View Code. Go to Insert - Module, and paste this in.

Function SumOverlap(NameRange As Range, xName As String, StartRange As Range, EndRange As Range) As Long
Dim newRange As Range
Dim xInt As Range
Dim xTot As Range
Dim xCounter As Long
Dim sumCount As Long
Dim c As Range

xCounter = 0
For Each c In NameRange
    xCounter = xCounter + 1
    'Check if it's a matching name
    If c.Value = xName Then
        'Build a fake range using dates as end points
        Set newRange = Range("A" & CLng(StartRange.Cells(xCounter).Value) & ":A" & CLng(EndRange.Cells(xCounter)))
     
        'Determine if we have any overlap
        If Not xTot Is Nothing Then Set xInt = Intersect(newRange, xTot)
     
        'If overlap, calculate how many days it was
        If Not xInt Is Nothing Then
            sumCount = xInt.Rows.Count
            Set xInt = Nothing
        End If
     
        'Add date range to our list of ranges
        If Not xTot Is Nothing Then
            Set xTot = Union(xTot, newRange)
        Else
            Set xTot = newRange
        End If
    End If
Next c

'Return # of overlap
SumOverlap = sumCount

End Function

Close the editor.
Back in the XL sheet, in cell B10, place this formula:
=SUMPRODUCT(($A$2:$A$6=A10)*($C$2:$C$6-$B$2:$B$6+1))-SumOverlap(A2:A6,A10,B2:B6,C2:C6)

You can copy the formula down to B11

You've not re-created the macro workbook above.

 

[Luke M]

If you can't download the file at all, you can see from the screenshot I took what my file looks like. Then in A10 and A11 I wrote the names Tom and Jerry. That should let your recreate my file from scratch, if needed.

 

[garythebadger]

Thank you very much Luke. VBA is new to me, but I have people I work with who can help me, if I can't figure it out, but it looks pretty straight-forward. And I can open this new attachment, its not blocked