Quote:
Originally Posted by sergeten
Hi there everyone!
There is one issue I cannot solve. Hope to get your help on this.
Basically I have 3 columns:
1) person's name;
2) date of arrival to the country;
3) departure date.
This data is info on employees of a company arriving to the country for the provision of services.
I have such data on several companies. I may need to do 2 things with data on each company depending on the circumstances:
1) I need to check the duration of stay of company's employees in the country in days. However, if 2 or more employees were present at any particular date, this day needs to be counted only once.
2) I need to check whether the duration of the stay of representatives of the company did not exceed 183 days in any consecutive 365-days period (not calendar year).
Thanks in advance.
Cheers.
|
Try this UDF:
Function DayCount(rng As Range, n As Long) As String
Dim Dmin As Date, Dmax As Date, DY As Date, i As Long, j As Long
Dim rng1 As Range, rng2 As Range, d As Range
Dim nc As Long, exceed As Boolean
If rng.Columns.Count < 2 Then Exit Function
Set rng1 = rng.Resize(, 1)
Set rng2 = rng.Offset(, 1).Resize(, 1)
For Each d In rng
If VarType(d.Value) < vbDate Then Exit Function
Next
For Each d In rng1
If d d.Offset(, 1) Then Exit Function
Next
Dmin = Application.min(rng)
Dmax = Application.max(rng)
ReDim cal(Dmax - Dmin) As Long
For Each d In rng1
For i = d To d.Offset(, 1)
cal(i - Dmin) = 1
Next
Next
For i = 0 To Dmax - Dmin - (n - 1)
DY = Application.min(i + 364, Dmax - Dmin)
For j = i To DY
nc = nc + cal(j)
Next
If nc = n Then
exceed = True
Exit For
End If
If exceed Then Exit Function
Next
DayCount = "Total days = " & Application.Sum(cal) & " - Company " & _
IIf(exceed, _
"exceed " & n & " days between " & i + Dmin & " and " & DY + Dmin, _
"NOT exceed " & n & " days in a year")
End Function
i.e.
=daycount(B3:C8,183)
where B3:C8 is the range containing dates of arrival and departure of a company's employees
Hi,
E.