Finding cases where client depart date and reentry date are less than 28 days apart

[giles]

I have the task of finding a way to find cases amongst 1,000s of clients where:
a) the client has entered the system and been closed; and
b) reentered the system again within 28 days of the last closure.

Being a novice I used sumproduct which counts clients ebtween 2 nominated dates but I cant figure out how to identify only those who renetered within a given timeframe - say 28 days.

Can Excel achieve this?

(I can't figure out how to attach a file! Sorry)

 

[texasalynn]

if you have the results then you could use that column of cells to do a conditional format of any that are <= 28

for example

Excel 2003

	A	B	C	D
1	Name	Enter	Re-enter	# of Days
2	Client A	4/2/2012	5/31/2012	59
3	Client B	5/7/2012	5/25/2012	18
4	Client D	6/14/2012	7/25/2012	41
5	Client Z	1/15/2012	4/25/2012	101

<COLGROUP><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet2​

Worksheet Formulas Cell Formula D2 =+C2-B2 D3 =+C3-B3 D4 =+C4-B4 D5 =+C5-B5 <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

then setup the conditional format as a formula is =$D2<=28

 

[giles]

Thank You Teasalynn

I understand your suggestion using conditional formating; that woudl identify those cases where te length of stay is less than 28 days for each case evealuated individually.

MY challenge is for a population of clients who may enter and leave multiple times finding those clients who reenter teh servcie less than 28 days since their last departure.

The datafile will have about 6,000 records.

I have used sumproduct that to give me the number of cases between two given dates; but i can't figure out how to have a formula able to count those cases where there has been a reenter less than 28 days sincetheir last departure.

any suggestions welcomed

Regards

Giles

if you have the results then you could use that column of cells to do a conditional format of any that are <= 28

for example

Excel 2003

	A	B	C	D
1	Name	Enter	Re-enter	# of Days
2	Client A	4/2/2012	5/31/2012	59
3	Client B	5/7/2012	5/25/2012	18
4	Client D	6/14/2012	7/25/2012	41
5	Client Z	1/15/2012	4/25/2012	101

<COLGROUP><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet2​

Worksheet Formulas Cell Formula D2 =+C2-B2 D3 =+C3-B3 D4 =+C4-B4 D5 =+C5-B5 <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

then setup the conditional format as a formula is =$D2<=28

 

[dcardno]

I assume that your file will include the data fields:
Client name (or other identifier)
Entry Date
Disposition (you mention the idea that an entry might be "closed" - I take it that would go here)

First, sort the file (or a copy, if the sort order is otherwise important) by client ID, by entry date
Create a 'helper' column with a formula to test if:
1) the client ID in this row is = to the row above
2) the entry date is <= 28 days after the date in the row above
3) the disposition in the row above is "closed" (ir relevant: that's up to you)

You have found a candidate only if all three conditions are met. You can highlight those cases by:
1) applying conditional formatting - that will help, but in 6K rows, you may miss some; it depends on how frequent candidate transactions are
2) if the test returns "TRUE" or "FALSE" you can search on that column for the next "TRUE" - that means that you shouldn't miss any; after dealing with it, just search again
3) You have identified the re-entries: if you have to discuss them with anyone, you may need the exits, as well: add another helper column that indicates that the current row is an exit that has to be paired with the following re-entry, and then a third helper column that totals (if they are numeric) or logically combines those two indicators, and save it by value, not as a formula. Now sort by this last indicator, client ID, and date - this should produce a list of all the closed / re-enter in < 28 day sequences, in order by client

 

[giles]

Thanks very much;

works a treat, I built an "and" within an "if" statement along the lines of your logic, with a bit of fiddling to overcome unforeseen results it works terrifically.

I was looking in the wrong places to be honest thinking some magic formulae that evaluated the whole dataset would tell me a number, but long hand is good. I have built a pivot table to identify those reenetered within 28 days and allows drill down etc.

So to everyone who puzzled over this one - Many Thanks

Giles

I assume that your file will include the data fields:
Client name (or other identifier)
Entry Date
Disposition (you mention the idea that an entry might be "closed" - I take it that would go here)

First, sort the file (or a copy, if the sort order is otherwise important) by client ID, by entry date
Create a 'helper' column with a formula to test if:
1) the client ID in this row is = to the row above
2) the entry date is <= 28 days after the date in the row above
3) the disposition in the row above is "closed" (ir relevant: that's up to you)

You have found a candidate only if all three conditions are met. You can highlight those cases by:
1) applying conditional formatting - that will help, but in 6K rows, you may miss some; it depends on how frequent candidate transactions are
2) if the test returns "TRUE" or "FALSE" you can search on that column for the next "TRUE" - that means that you shouldn't miss any; after dealing with it, just search again
3) You have identified the re-entries: if you have to discuss them with anyone, you may need the exits, as well: add another helper column that indicates that the current row is an exit that has to be paired with the following re-entry, and then a third helper column that totals (if they are numeric) or logically combines those two indicators, and save it by value, not as a formula. Now sort by this last indicator, client ID, and date - this should produce a list of all the closed / re-enter in < 28 day sequences, in order by client

 

[T. Valko]

Thanks very much;

works a treat, I built an "and" within an "if" statement along the lines of your logic, with a bit of fiddling to overcome unforeseen results it works terrifically.

I was looking in the wrong places to be honest thinking some magic formulae that evaluated the whole dataset would tell me a number, but long hand is good. I have built a pivot table to identify those reenetered within 28 days and allows drill down etc.

So to everyone who puzzled over this one - Many Thanks

Giles

I may be misunderstanding what you want...

Wouldn't something like this do what you want:

=SUMPRODUCT(--(Re-enter range-Closed range<=28))

 

[giles]

Hi T,

yes that formula will calculate the length of stay for each individual case

Where the test is different is when Case A has a number of "stays", the test is the time between the Closed date and the next Enter date to see if it is less than 28 days.

Hope this explains the difference.

The test is over 1,000s of cases.

Regards

Giles

I may be misunderstanding what you want...

Wouldn't something like this do what you want:

=SUMPRODUCT(--(Re-enter range-Closed range<=28))