如何用Excel公式生成每月各周日期范围（首周始于月初、止于周日）

核心逻辑 #

基于单元格F1的年月（输入任意当月日期，格式设为yyyy年m月），为每个周工作表自动生成对应日期范围：

• 首周固定从当月1日开始，到当月第一个周日结束
• 中间周为完整的7天周期（周一至周日）
• 最后一周固定从上周结束日次日开始，到当月最后一天结束

实现步骤 #

1. 标记周序号：在每个周工作表的任意空白单元格（比如B1）输入该表对应的周数（第1周填1，第2周填2，以此类推）
2. 输入公式：在A5单元格粘贴以下公式（支持Excel 365/2021及以上版本，因用到LET函数简化逻辑）：

=LET(
    当月首日, DATE(YEAR(F1), MONTH(F1), 1),
    当月末日, EOMONTH(F1, 0),
    首周末日, 当月首日 + (7 - WEEKDAY(当月首日, 2)),
    周开始日, IF(B1=1, 当月首日, 首周末日 + 1 + (B1-2)*7),
    周结束日, IF(B1=1, 首周末日, MIN(周开始日 + 6, 当月末日)),
    TEXT(周开始日, "yyyy年m月d日") & "-" & TEXT(周结束日, "yyyy年m月d日")
)

公式拆解 #

• 当月首日：提取F1对应的当月第一天，避免手动输入日期出错
• 当月末日：用EOMONTH函数快速获取当月最后一天
• 首周末日：通过WEEKDAY(...,2)（周一=1、周日=7）计算当月第一个周日的日期，若1号本身是周日则直接取1号
• 周开始日：首周直接取当月1日；后续周从首周结束日的次日开始，每往后一周加7天
• 周结束日：首周取计算好的周日；后续周先按7天周期计算，若超过当月最后一天则自动替换为当月末日
• TEXT函数：将日期格式化为指定的中文日期样式

兼容旧版Excel（无LET函数） #

如果使用Excel 2019及更早版本，可使用嵌套IF的长公式（逻辑一致，只是重复计算较多）：

=IF(B1=1,
    TEXT(DATE(YEAR(F1),MONTH(F1),1),"yyyy年m月d日")&"-"&TEXT(DATE(YEAR(F1),MONTH(F1),1)+(7-WEEKDAY(DATE(YEAR(F1),MONTH(F1),1),2)),"yyyy年m月d日"),
    TEXT(DATE(YEAR(F1),MONTH(F1),1)+(7-WEEKDAY(DATE(YEAR(F1),MONTH(F1),1),2))+1+(B1-2)*7,"yyyy年m月d日")&"-"&TEXT(MIN(DATE(YEAR(F1),MONTH(F1),1)+(7-WEEKDAY(DATE(YEAR(F1),MONTH(F1),1),2))+1+(B1-2)*7+6,EOMONTH(F1,0)),"yyyy年m月d日")
)

示例验证 #

• 2024年1月（1号为周一）：
  • 第1周：2024年1月1日-2024年1月7日
  • 第4周：2024年1月22日-2024年1月28日
  • 第5周：2024年1月29日-2024年1月31日
• 2024年2月（1号为周四）：
  • 第1周：2024年2月1日-2024年2月4日
  • 第4周：2024年2月19日-2024年2月25日
  • 第5周：2024年2月26日-2024年2月29日

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