traveling days

[bond345]

I have raw data in Excel, but need to calculate total number of
company days of time spent on certain projects. For example I have 3
employess who spend the following days on the following project.

Employees Days Working Individual Time on Project
Emp. 1 1/1/2007-1/5/2007 5 days
Emp. 2 1/2/2007-1/6/2007 5 days
Emp. 3 1/20-2007-1/25/2007 5 days

Total Company Time on
Project 11 days

The total is 11 days of company time on the project because of the
overlapping days on Emp. 1 and Emp. 2. I have the days working of
these employees, but need to calculate that raw data into total
company days spent on each project. Any assistance is appreciated.

 

[Nigel]

One option (if the data is sorted by the first date to latest) and that the
first date and last date are in separate columns e.g

Emp1 1/1/2007 1/5/2007
Emp2 2/1/2007 1/6/2007
Emp3 1/20/2007 1/25/2007

In column 3 you then test if the first date is greater or less than the
previous row last date, if it is greater then compute the gap for the row
(last-first date +1), if it is less then take the difference between the
last date prior row and the first data current row and subtract that from
the difference between the last and first date in the current row. Copy the
formula down for the required number of rows.

So for the above you should see 5 in the first row, 1 in the second row and
6 in the last row, making a total of 12 days. ( your post showing 5 in the
last row should be 6?)

 

[Chip Pearson]

By my reckoning, your example has 12 days, not 11. The days worked by any
employee are

Jan-1, 2, 3, 4, 5, 6, 20, 21, 22, 23, 24, 25

You can use the following VBA function to calculate company days. DateRange
is a 2 column range containing the start and end dates for each employee,
with the start date in the first column and the end date in the second
column. The IgnoreWeekends parameter indicates whether to exclude weekend
days (Saturday and Sunday) from the count. If this parameter is True or
omitted, weekends are not counted. If this parameter is False, weekend days
are included in the count.

If an invalid date is encountered, the function returns a #VALUE error.

Function CompanyDays(DateRange As Range, _
Optional IgnoreWeekEnds As Boolean = True) As Variant
Dim FirstDate As Long
Dim LastDate As Long
Dim RR As Range
Dim N As Long
Dim Arr() As Long

On Error Resume Next
With Application.WorksheetFunction
Err.Clear
FirstDate = .Min(DateRange.Columns(1))
If Err.Number <> 0 Then
CompanyDays = CVErr(xlErrValue)
Exit Function
End If

LastDate = .Max(DateRange.Columns(2))
If Err.Number <> 0 Then
CompanyDays = CVErr(xlErrValue)
Exit Function
End If

If LastDate < FirstDate Then
CompanyDays = CVErr(xlErrValue)
Exit Function
End If

Err.Clear
ReDim Arr(FirstDate To LastDate)
If Err.Number <> 0 Then
CompanyDays = CVErr(xlErrValue)
Exit Function
End If
For Each RR In DateRange.Columns(1).Cells
For N = CLng(RR.Value) To CLng(RR(1, 2).Value)
If IgnoreWeekEnds = True Then
If Weekday(N, vbMonday) <= 5 Then
Arr(N) = 1
End If
Else
Arr(N) = 1
End If
Next N

Next RR
CompanyDays = .Sum(Arr)
End With

End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)