Forum Discussion
Filter rows where difference between dates is lower than 31 days
Hello, have a dataset where I want to filter out (or combine) rows when the difference between the enddate of this row and the startdate of another row is lower than 31 days. The dataset look...
Hi!
Yes, I can.
What I do in Excel is that I make additional columns to "group" the existing table. I use the following formulas:
Columns F, G, H and I make a new grouped table (how I want it) with only data in rows 3, 5 and 7.
| A | B | C | D | E | F | G | H | I | |
| ClientID | Product | Startdate | Enddate | Startdate (new) | Startdate (new 2) | Enddate (new) | Product (new) | ClientID (new) | |
| 1 | 123 | 001 | 1-1-17 | 31-1-17 | =C1 --> 1-1-17 | =IF(E1=E2;"";E1) --> "" | =IF(E1=E2;"";D1) --> "" | =IF(E1=E2;"";B1) --> "" | =IF(E1=E2;"";A1) --> "" |
| 2 | 123 | 001 | 1-2-17 | 28-2-17 | =IF(AND( A2=A1;B2=B1;C2<(D1+31)); E1; C1) --> 1-1-17 | =IF(E2=E3;"";E2) --> "" | =IF(E2=E3;"";D2) --> "" | =IF(E2=E3;"";B2) --> "" | =IF(E2=E3;"";A2) --> "" |
| 3 | 123 | 001 | 1-3-17 | 31-3-17 | =IF(AND( A3=A2;B3=B2;C3<(D2+31)); E2; C2) --> 1-1-17 | =IF(E3=E4;"";E3) --> 1-1-17 | =IF(E3=E4;"";D3) --> 31-3-17 | =IF(E3=E4;"";B3) --> 001 | =IF(E3=E4;"";A3) --> 123 |
| 4 | 123 | 001 | 1-8-17 | 30-8-17 | =IF(AND( A4=A3;B4=B3;C4<(D3+31)); E3; C3) --> 1-8-17 | =IF(E4=E5;"";E4) --> "" | =IF(E4=E5;"";D4) --> "" | =IF(E4=E5;"";B4) --> "" | =IF(E4=E5;"";A4) --> "" |
| 5 | 123 | 001 | 1-9-17 | 31-9-17 | =IF(AND( A5=A4;B5=B4;C5<(D4+31)); E4; C4) --> 1-8-17 | =IF(E5=E6;"";E5) --> 1-8-17 | =IF(E5=E6;"";D5) --> 31-9-17 | =IF(E5=E6;"";D5) --> 001 | =IF(E5=E6;"";A5) --> 123 |
| 6 | 123 | 002 | 1-1-17 | 31-1-17 | =IF(AND( A6=A5;B6=B5;C6<(D5+31)); E6; C6) --> 1-1-17 | =IF(E6=E7;"";E6) --> "" | =IF(E6=E7;"";D6) --> "" | =IF(E6=E7;"";B6) --> "" | =IF(E6=E7;"";A6) --> "" |
| 7 | 123 | 002 | 15-2-17 | 31-3-17 | =IF(AND( A7=A6;B7=B6;C7<(D6+31)); E7; C7) --> 1-1-17 | =IF(E7=E8;"";E7) --> 1-1-17 | =IF(E7=E8;"";D7) --> 31-3-17 | =IF(E7=E8;"";D7) --> 002 | =IF(E7=E8;"";D7) --> 123 |
| 8 | 123 | 002 | 1-9-17 | 31-9-17 | =IF(AND( A8=A7;B8=B7;C8<(D7+31)); E8; C8) --> 1-9-17 |
Hopefully you can help me with this one!
Thanks!
Niels