Mark if checkin date is within 30 days of checkout date where name isthe same.

[steve1040]

All I have the following function which I got from here and it is
very
slow but I thought it was working until I noticed that sometimes the
first occurance of a name is still appearing as within 30 days. What
is causing this? Is it because name can appear more than twice?

I need to mark any row where the checkin date is within 30 days of
checkout date of previous record where name is the same.

Function within30(Name As Range, NameRng As Range, Admit As Range,
Discharge As Range) As String
Application.Volatile
Dim NameCount As Long
Dim ChkOut As Long
Dim ChkIn As Long
Dim f As Range
NameCount = Application.WorksheetFunction.CountIf(NameRng, Name)
If NameCount = 1 Then
within30 = ""
Exit Function
End If
For n = 1 To NameCount
For r = Name.Row + 1 To NameRng.Rows.Count - 1
Debug.Print r
If Cells(r, Name.Column) = Name Then
ChkOut = Cells(Name.Row, Discharge.Column)
ChkIn = Cells(r, Admit.Column)
If ChkIn - ChkOut <= 30 Then
within30 = "X"
Exit For
End If
Set Name = Cells(r, Name.Column)
End If
Next
Next
End Function

 

[Bob Phillips]

It is very hard to hep without knowing what the data looks like and what all
of the argument values are when called.

 

[steve1040]

Data looks like this
1 Name|Checkin|Checkout
2 Dan Jones|Jan-1-10|Jan-2-10
3 Dan Jones|Mar-5-10|Mar-6-10
4 Dan Jones|May-1-10|May-8-10
5 John Doe|May-15-10|May-16-10
6 John Doe|May-30-10|Jun-3-10
7 John Doe|Jul-6-10|Jul-9-10
8 Sarah Cakes|Jan-10-10|Jan-15-10
9 Sarah Cakes|May-3-10|May-5-10
10 Sarah Cakes|Jul-17-10|Jul-18-10
11 Sarah Cakes|Jul-20-10|Jul-27-10

Row number 5 & 11 should be marked with a "X"

Thanks again
Steve

 

[Ms-Exl-Learner]

Hi Steve,

Worksheet Function Solution:-

I assume that your sample data starts from A1 cell and ends with D11
like the beow:-

Your Column Headers stands in 1st Row.

Row/Col| A B C
Row1 NAME CHECKIN CHECKOUT
Row2 Dan Jones 1-Jan-10 2-Jan-10
Row3 Dan Jones 5-Mar-10 6-Mar-10
Row4 Dan Jones 1-May-10 8-May-10
Row5 John Doe 15-May-10 16-May-10
Row6 John Doe 30-May-10 3-Jun-10
Row7 John Doe 6-Jul-10 9-Jul-10
Row8 Sarah Cakes 10-Jan-10 15-Jan-10
Row9 Sarah Cakes 3-May-10 5-May-10
Row10 Sarah Cakes 17-Jul-10 18-Jul-10
Row11 Sarah Cakes 20-Jul-10 27-Jul-10

Copy and paste the below formula in D2 cell:-

=IF(AND($A2="",$B2=""),"",IF(OR($A2="",$B2=""),"Input Req. In [Col-A
OR Col-B] Cell",IF(ISERROR(DATEDIF(INDEX($C$1:$C1,MATCH(1,($A$1:$A1=
$A2)*($C$1:$C1<$B2),1)),$B2,"D")),"",IF(DATEDIF(INDEX($C$1:$C1,MATCH(1,
($A$1:$A1=$A2)*($C$1:$C1<$B2),1)),$B2,"D")<=30,"X",""))))

Now place the cursor in D2 cell and press F2 button and press CNTRL
+SHIFT+ENTER, since it is an ARRAY FORMULA we need to hit CNTRL+SHIFT
+ENTER instead of general enter. General enter wont work with the
above formula.

After hitting the CNTRL+SHIFT+ENTER the formula will be covered with
Curly Braces {} like the below:-

{=IF(AND($A2="",$B2=""),"",IF(OR($A2="",$B2=""),"Input Req. In [Col-A
OR Col-B] Cell",IF(ISERROR(DATEDIF(INDEX($C$1:$C1,MATCH(1,($A$1:$A1=
$A2)*($C$1:$C1<$B2),1)),$B2,"D")),"",IF(DATEDIF(INDEX($C$1:$C1,MATCH(1,
($A$1:$A1=$A2)*($C$1:$C1<$B2),1)),$B2,"D")<=30,"X",""))))}

Don’t add the curly braces manually. After hitting CNTRL+SHIFT+ENTER
excel will automatically add the Curly Braces in the above formula.

Hope it’s clear to you!

 

[Ms-Exl-Learner]

Forget to mention another step.

Drag the D2 cell formula to the remaining cells of Column-D based on
the A and B Column Data.