Excel跨年度时如何将周数字符串转换为对应周的正确起止日期

问题复盘 #

• 现有表格weeknum列使用公式 =YEAR([@[Date]])&"/"&WEEKNUM([@[Date]],2) 生成2022/10格式的年/周数字符串，规则为周一作为一周起始，1月1日所在周为当年第1周，需要根据该字符串计算对应周的起始、结束日期。
• 原公式 =DATE($M$1,1,-2)-(WEEKDAY(DATE($M$1,1,3))+(MID(M4,6,2)*7)) 存在核心逻辑错误：整体使用减法计算偏移，周数乘7后数值越大，减去的总偏移量越大，返回日期就越早，才会出现周数越大日期越小的异常；同时基准日期计算偏差，仅2021/53刚好凑对结果，其余周数全部计算错误。
• 注：原weeknum生成公式存在笔误，[@[Date]}的结尾大括号应改为方括号，正确写法为=YEAR([@[Date]])&"/"&WEEKNUM([@[Date]],2)。

正确公式（完全匹配WEEKNUM(,2)规则） #

以下公式直接从年周字符串中提取年份、周数计算，不需要单独引用年份单元格，避免引用值和字符串内年份不匹配的错误：

• 周起始日期（周一）：

=MID(M4,6,2)*7 + DATE(LEFT(M4,4),1,1) - WEEKDAY(DATE(LEFT(M4,4),1,1),2) - 5

• 周结束日期（周日，即起始日+6天）：

=MID(M4,6,2)*7 + DATE(LEFT(M4,4),1,1) - WEEKDAY(DATE(LEFT(M4,4),1,1),2) + 1

公式逻辑说明 #

1. LEFT(M4,4)提取字符串前4位得到年份，MID(M4,6,2)提取斜杠后2位得到周数
2. 以当年1月1日为基准，通过WEEKDAY(,2)得到1月1日对应周几（周一返回1，周日返回7）
3. 按每周7天的偏移量累加周数，修正基准日偏移后得到对应周的周一日期，加6天即为当周周日
4. 验证：计算2022/10时返回起始日为2022/2/28，结束日为2022/3/6，和预期结果完全一致，周数越大日期越晚，无反向异常。

如果需要保留原有$M$1引用年份的写法，把公式中LEFT(M4,4)替换为$M$1即可。

内容的提问来源于stack exchange，提问作者P002143_k