Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mark if checkin date is within 30 days of checkout date where name isthe same.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mark if checkin date is within 30 days of checkout date where name is the same.
It is very hard to hep without knowing what the data looks like and what all
of the argument values are when called. -- HTH Bob "steve1040" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mark if checkin date is within 30 days of checkout date wherename is the same.
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 On Jul 10, 2:23*am, "Bob Phillips" wrote: It is very hard to hep without knowing what the data looks like and what all of the argument values are when called. -- HTH Bob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mark if checkin date is within 30 days of checkout date wherename is the same.
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=""),"In put 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(IN DEX($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=""),"I nput 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(IN DEX($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 ----------------------- On Jul 12, 3:42*pm, steve1040 wrote: 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 On Jul 10, 2:23*am, "Bob Phillips" wrote: It is very hard to hep without knowing what the data looks like and what all of the argument values are when called. -- HTH Bob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mark if checkin date is within 30 days of checkout date wherename is the same.
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. ----------------------- Ms-Exl-Learner ----------------------- On Jul 12, 3:42*pm, steve1040 wrote: 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 On Jul 10, 2:23*am, "Bob Phillips" wrote: It is very hard to hep without knowing what the data looks like and what all of the argument values are when called. -- HTH Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Turns Green 90-Days before the date shown in cell | Excel Worksheet Functions | |||
business day date from a specific date based on a number of days | Excel Worksheet Functions | |||
Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions |