Re: traveling days
- From: "Chip Pearson" <chip@xxxxxxxxxxxx>
- Date: Sat, 1 Dec 2007 06:12:19 -0600
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)
<bond345@xxxxxxxxx> wrote in message news:412d6d82-3ef6-4c19-84f7-86700333d79e@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.
.
- Prev by Date: Re: Help! Jumping ListViews!
- Next by Date: Re: Code to Change Sheet Name
- Previous by thread: Re: Help! Jumping ListViews!
- Next by thread: Have A1 in Top left corner of screen Q
- Index(es):
Relevant Pages
|
