Counting number of days in several periods ignoring repeating dates
 

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.

Wow, m8, u blew my mind... Thanks a lot! This was my very first experience with an UBF function. I had to read additional info on the web in order to get how it works. =) I really appreciate your help.

Well, I managed to start this UBF. It does count the number of days correctly, but for some reason it fails 183 days check. I used it on one example and it told me that the company exceeded 183 days threshold even though the total number of days was less than 183 =(

Could you correct it for me please? Thank you in advance.

Cheers,m8.

You're right, there is an oversight...

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)
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)
nc = 0 'I neglected this
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


E.

M8, u r genius!!!

Thank you so much!!!

I have 2 questions:

1) would that be possible to split the function, i.e. have 2 formulas, where one would count number of days and show the result simply as a number without any text and the other one would just say "yes" if the company's presence in the country exceeded 183 days within any consecutive 365-days period and "no" if it did not.
2) i am not very familiar with how UDF function works, but is that possible to make 2 modules work in the same excel file?

Thanks again for your help! I really appreciate!

Here's your distinct functions:
DCount(range)
Exceed(range, number_of_days)

__________________
Function DCount(rng As Range) As Long
Dim Dmin As Date, Dmax As Date, i As Long, d As Range
If rng.Columns.Count < 2 Then Exit Function
Dmin = Application.min(rng)
Dmax = Application.max(rng)
Set rng = rng.Resize(, 1)
ReDim cal(Dmax - Dmin) As Long
For Each d In rng
If VarType(d.Value) < vbDate Then Exit Function
If d d.Offset(, 1) Then Exit Function
For i = d To d.Offset(, 1)
cal(i - Dmin) = 1
Next
Next
DCount = Application.Sum(cal)
End Function
__________________

Function Exceed(rng As Range, n As Long) As String
Dim Dmin As Date, Dmax As Date, DY As Date, d As Range
Dim nc As Long, i As Long, j As Long, exc As Boolean
If rng.Columns.Count < 2 Then Exit Function
Dmin = Application.min(rng)
Dmax = Application.max(rng)
Set rng = rng.Resize(, 1)
ReDim cal(Dmax - Dmin) As Long
For Each d In rng
If VarType(d.Value) < vbDate Then Exit Function
If d d.Offset(, 1) Then Exit Function
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)
nc = 0
For j = i To DY
nc = nc + cal(j)
Next
If nc = n Then
exc = True
Exit For
End If
Next
Exceed = IIf(exc, "Yes", "No")
End Function
__________________

Hi,
E.