PDA

View Full Version : fill in sequence number



backspace20
01-14-2016, 12:03 AM
I have a sequence of dates. I want to consider these dates as a group if the difference between them is less than 20 days (The condition).
For example the sequence of below dates can be divided in to two groups :



9/13/2013


9/20/2013


9/27/2013


10/4/2013


11/24/2013


12/1/2013


12/8/2013


12/15/2013


12/22/2013


12/29/2013



1-From 9/13/2013 to 10/4/2013 (The difference between dates is less than 20 days.)

The difference between 10/4/2013 and 11/24/2013 is more than 20 days. As consequence, the second group starts from 11/24/2013

2-From 11/24/2013 to 12/29/2013 (The difference between dates is less than 20 days).

I want to add a column to this table to show these sequence:




9/13/2013

1-Initial


9/20/2013

2


9/27/2013

3


10/4/2013

4-End


11/24/2013

1-Initial


12/1/2013

2


12/8/2013

3


12/15/2013

4


12/22/2013

5


12/29/2013

6-End








How can i do that? Thanks

Bob Phillips
01-14-2016, 02:27 AM
By my calculations, 4th Oct is 21 days after 13th Sep, so the first end is 27th Sep not 4th Oct.

And as 24th Nov is 45 days after 4th Oct, 4th Oct is both Initial and End, how is that shown?

backspace20
01-14-2016, 02:41 AM
Sorry,I meant the difference between to consecutive dates must not be more than 20 days. The difference between 13-Sep & 20-sep, 20-sep & 27-sep, 27-Sep & 4-oct is less than 20 days. so, they belong to one group. The difference between 4 oct & 24-No is more than 20 days, so the next group starts from 24 of Nov and so on

snb
01-14-2016, 03:16 AM
In B2:


=IF(A2-A1>20;MAX(B$1:$B1)+1;"")

Bob Phillips
01-14-2016, 03:45 AM
Put these nvalues/formulas in cells

C2: Initial
C3: =IF(OR($A3-$A2>20,$A3=""),"End",IF($A2-$A1>20,"Initial",""))

copy C3 down

B2: 1
B3: =IF($C2="Initial",1,$B1+1)

copy B3 down

backspace20
01-14-2016, 04:17 AM
Thanks,
But how can I do it in vba?

SamT
01-14-2016, 12:32 PM
How would you handle these dates?


1/1


1/6


1/11


1/16


1/21


1/26


1/31


2/5


2/10


2/15


2/20


2/25


3/2


3/7


3/12


3/17


3/22


3/27


4/1


4/6


4/11


4/16


4/21