来自每个用户的自定义周号

Question

是否有方法计算从用户的第一个事务日期开始的自定义周数？用户(emailId)和TransDate列可能不处于排序状态，如下所示：

例如：

+------+-------------+---------------------+
| WkNo |  TransDate  |       emailId       |
+------+-------------+---------------------+
|    1 | 2018-Aug-30 | moz.shea@abc.com    |
|    1 | 2018-Aug-30 | moz.shea@abc.com    |
|   10 | 2018-Nov-07 | moz.shea@abc.com    |
|    1 | 2018-Aug-09 | zabi.prado@abc.com  |
|    1 | 2018-Aug-09 | zabi.prado@abc.com  |
|    6 | 2018-Sep-20 | zabi.prado@abc.com  |
|   15 | 2018-Nov-23 | zabi.prado@abc.com  |
|   21 | 2018-Dec-31 | zabi.prado@abc.com  |
|    1 | 2018-Aug-20 | silo.whitte@abc.com |
|    5 | 2018-Sep-23 | silo.whitte@abc.com |
|    7 | 2018-10-11  | silo.whitte@abc.com |
|    7 | 2018-10-11  | silo.whitte@abc.com |
|    8 | 2018-Oct-14 | silo.whitte@abc.com |
|    9 | 2018-Oct-19 | silo.whitte@abc.com |
|    1 | 2018-Jul-01 | pablo.gucci@abc.com |
|    6 | 2018-Aug-10 | pablo.gucci@abc.com |
|   13 | 2018-Oct-03 | pablo.gucci@abc.com |
+------+-------------+---------------------+

我使用FILTER函数编写了以下公式，然后将每个用户的过滤日期提供给DATEDIF函数。然而，我并没有像上面所示的那样得到预期的结果。

=ARRAYFORMULA(if(B2:B="","",1 + round(DATEDIF(min(sort(FILTER(B2:B,C2:C=C2:C),1,true)),sort(FILTER(B2:B,C2:C=C2:C),1,true),"D")/7)))

编辑：

公式结果：

1
7
7
7
8
10
10
13
13
14
16
16
16
17
19
22
27

还从上述公式中删除排序：

=ARRAYFORMULA(if(B2:B="","",1 + round(DATEDIF(min(sort(FILTER(B2:B,C2:C=C2:C),1,true)),FILTER(B2:B,C2:C=C2:C),"D")/7)))

公式结果：

10
10
19
7
7
13
22
27
8
13
16
16
16
17
1
7
14

两者似乎都能工作，但是给出意外的结果，因为MIN正在计算单个日期的2018-Jul-01，而不是每个用户的最小日期数组。我哪里出问题了？

Answer 1

对于那些可能面临类似挑战的人来说，以下是答案：

=ARRAYFORMULA(IF(B2:B="","",ROUND((B2:B-VLOOKUP(C2:C,SORT({C2:C,B2:B},2,1),2,0))/7)+1))

其思想是在C列上执行一个Vlookup，按照升序传递一个切换的和排序的日期范围。然后从B列日期中扣除这些第一个日期，以获得预期的结果。不管是几天还是几周。

人们可以删除+1，因为我使用它只是将开始的一周显示为1而不是0。因此，结果可能略有不同。但是如果没有+1，则结果是准确的，特别是当您正在执行队列时。

0
0
10
0
0
6
15
21
0
5
7
7
8
9
0
6
13

作为检查，我删除除法7和+1，然后检查天，这是正确的。

0
0
69
0
0
42
106
144
0
34
52
52
55
60
0
40
94

希望这能有所帮助。

Answer 2

您试过使用MINIFS()函数吗？

例如：

细胞B2:B10电子邮件的日期C2:C10

单元格A2中的公式将给出单元格C2中电子邮件地址的最早日期

=MINIFS($B$2:$B$10, $C2:$C10, "="&C2)

您应该能够在公式中使用此方法来计算周数。