excel - 识别跨年同月同日的日期

Question

所以我得到了完整的日期列表。我需要找出哪些日期属于我每年规定的“开始日期”和“结束日期”的同一范围(日和月)。

例如对于开始日期:2/12/2004，结束日期:31/1/2005，日期介于 2/12/2000 和 31/1/2001、2/12/2001 和 31/1/2002、2/12/2001 年和 31/1/2002 等应标记为“有效”。

目前我使用上限/下限，然后测试日期是否落在该范围内。

Excel 公式:

上:=DATE(年(日期),月(测试),日(测试)+回顾期)

下:=DATE(年(日期),月(测试),日(测试)-回顾期)

有效:=IF(AND(日期<=上限,日期>=下限),1,0)

Test                    1/1/2005
Look Back Period (Days) 30
Start Date              2/12/2004 (=Test + Look Back Period)
End Date                31/1/2005 (=Test - Look Back Period)


    Date        Upper       Lower       Valid
    04/01/2000  31/1/2000   2/12/1999   1
    05/01/2000  31/1/2000   2/12/1999   1
    06/01/2000  31/1/2000   2/12/1999   1
    07/01/2000  31/1/2000   2/12/1999   1
    10/01/2000  31/1/2000   2/12/1999   1
    11/01/2000  31/1/2000   2/12/1999   1
    12/01/2000  31/1/2000   2/12/1999   1
    13/01/2000  31/1/2000   2/12/1999   1
    14/01/2000  31/1/2000   2/12/1999   1
    17/01/2000  31/1/2000   2/12/1999   1
    18/01/2000  31/1/2000   2/12/1999   1
    19/01/2000  31/1/2000   2/12/1999   1
    20/01/2000  31/1/2000   2/12/1999   1
    21/01/2000  31/1/2000   2/12/1999   1
    24/01/2000  31/1/2000   2/12/1999   1
    25/01/2000  31/1/2000   2/12/1999   1
    26/01/2000  31/1/2000   2/12/1999   1
    27/01/2000  31/1/2000   2/12/1999   1
    28/01/2000  31/1/2000   2/12/1999   1
    31/01/2000  31/1/2000   2/12/1999   1
    01/02/2000  31/1/2000   2/12/1999   0
    02/02/2000  31/1/2000   2/12/1999   0
    03/02/2000  31/1/2000   2/12/1999   0
    04/02/2000  31/1/2000   2/12/1999   0
    07/02/2000  31/1/2000   2/12/1999   0
    .
    .
    .
    30/11/2000  31/1/2000   2/12/1999   0
    01/12/2000  31/1/2000   2/12/1999   0
    04/12/2000  31/1/2000   2/12/1999   0 <-- :(
    05/12/2000  31/1/2000   2/12/1999   0 <-- :(
    06/12/2000  31/1/2000   2/12/1999   0 <-- :(
    07/12/2000  31/1/2000   2/12/1999   0 <-- :(
    08/12/2000  31/1/2000   2/12/1999   0 <-- :(
    11/12/2000  31/1/2000   2/12/1999   0 <-- :(
    12/12/2000  31/1/2000   2/12/1999   0 <-- :(
    13/12/2000  31/1/2000   2/12/1999   0 <-- :(
    14/12/2000  31/1/2000   2/12/1999   0 <-- :(
    15/12/2000  31/1/2000   2/12/1999   0 <-- :(
    18/12/2000  31/1/2000   2/12/1999   0 <-- :(
    19/12/2000  31/1/2000   2/12/1999   0 <-- :(
    20/12/2000  31/1/2000   2/12/1999   0 <-- :(
    21/12/2000  31/1/2000   2/12/1999   0 <-- :(
    22/12/2000  31/1/2000   2/12/1999   0 <-- :(
    25/12/2000  31/1/2000   2/12/1999   0 <-- :(
    26/12/2000  31/1/2000   2/12/1999   0 <-- :(
    27/12/2000  31/1/2000   2/12/1999   0 <-- :(
    28/12/2000  31/1/2000   2/12/1999   0 <-- :(
    29/12/2000  31/1/2000   2/12/1999   0 <-- :(
    01/01/2001  31/1/2001   2/12/2000   1
    02/01/2001  31/1/2001   2/12/2000   1
    03/01/2001  31/1/2001   2/12/2000   1
    04/01/2001  31/1/2001   2/12/2000   1
    05/01/2001  31/1/2001   2/12/2000   1
    08/01/2001  31/1/2001   2/12/2000   1
    09/01/2001  31/1/2001   2/12/2000   1
    10/01/2001  31/1/2001   2/12/2000   1
    11/01/2001  31/1/2001   2/12/2000   1

只要年份之间的期间不交叉，该公式就可以正常工作。但是，如果上限和下限跨越 2 个不同年份，则有效公式将排除上一年的值。

有什么解决办法吗？

Answer 1

要使用公式执行此操作，请再设置两个辅助列

lower1: =DATE(YEAR(Date)-1,MONTH(StartDate),DAY(StartDate))
upper1: =C8+2*LookBack
Lower2: =DATE(YEAR(Date),MONTH(StartDate),DAY(StartDate))
Upper2: =Lower2+2*LookBack

valid:  =IF(OR(AND(Date>=lower1,Date<=upper1),AND(Date>=Lower2,Date<=Upper2)),1,0)

我们所做的只是设置两组边界。相对于Date，从上一年开始；另一个从当前年份开始。然后检查日期以查看它是否属于任一集合。

对于外观，不要求边界列可见，甚至不要求它们与 Date 和 valid 列相邻。