Excel日期转星期全攻略：六种方法从入门到自动化，解决你的星期统计难题

先说个常见场景：你手里是一张销售明细表，日期列从1月排到12月，领导让你按星期维度出个统计。你打算把日期转化为星期，脑子里第一反应可能是“我手敲一个周一、周二就好”，结果两千行数据直接劝退。其实Excel里做这件事的路子远比你想象得多，从最简单的TEXT函数到不需要公式的快速填充，再到能一键刷新的大数据方案，我数了数至少有六种。这篇文章我把每个方案都捋一遍，包括适用场景、容易踩的坑，以及我自己实际用下来觉得最顺手的选型逻辑，算是一份可以直接抄作业的参考。

1. 先说清楚：Excel里的“日期”和“星期”都是什么

1.1 日期本质是序列数字，不是文字

很多人对日期转化星期这件事感到迷惑，根源在于没有理解Excel里日期的存储方式。你在单元格里看到“2024/1/15”，以为它是一个文本，其实Excel底层存的是一个整数——在Windows版Excel默认的1900日期系统里，1900年1月1日对应序列数1，之后每过一天数字加1。2024年1月15日对应的序列数大概在四万五千多，具体是多少不重要，你只要记住：日期在Excel眼里就是一个会“累加”的数字。

这就解释了为什么某些日期列会有绿色小三角、VLOOKUP匹配不上、求和等于0。因为这些列里的“日期”其实是文本，不是真正的日期序列数。日期转星期也一样，如果源数据本身就是文本，后面几个方法都会失效或者结果诡异。我处理数据前第一件事就是先确认日期列是真日期，方法很简单：选中日期列，看单元格格式是不是“日期”，或者用=ISNUMBER(A2)判断一下，返回TRUE才是真日期，返回FALSE说明是文本，要先转换。

1.2 “星期几”至少有四种写法，需求不同选型完全不同

“把日期变成星期”这句话其实很模糊。你要的输出到底长什么样？是纯数字1到7，还是中文“一”到“日”，还是“周一”到“周日”，还是“星期一”到“星期日”？这四种结果看起来差不多，但在Excel里的实现路径完全不同。

我自己一般这么分类：

• 数字型：周一=1，周二=2……适合程序判断、条件格式、排序。
• 单字型：一、二、三……适合做紧凑展示。
• 简称型：周一、周二……适合表格列标题、图表坐标轴。
• 全称型：星期一、星期二……适合周报正文、对外报表。

搞清楚需求再选方法，不然很容易做完了才发现格式不对，返工是一回事，关键是浪费的时间完全不值。

1.3 别忽略1900和1904日期系统这个历史遗留坑

这是新手最容易忽略的问题，也是很多“公式在别人电脑上结果不一样”的元凶之一。Windows版Excel默认使用1900日期系统，但Mac版Excel和老版本的一部分文件可能使用1904日期系统，两者之间差了1462天。对直接显示星期几来说，TEXT和WEEKDAY这类函数会自动适配，一般没问题；可是如果你准备用MOD函数直接从序列数推星期，那就要注意了，1904系统下同样的公式结果会整体偏移。

排查方法很简单：文件→选项→高级，往下翻看到“使用1904日期系统”是否被勾选。如果勾选了，要么尽量用TEXT/WEEKDAY这类高级函数，要么在MOD公式里加上补偿偏移。后面讲MOD的时候我会专门再提一次。

2. 用TEXT函数直接格式化，最顺手的方案

2.1 一套代码看清四种格式

TEXT函数可以说是日期转星期里最零门槛的方案，语法就一行：

=TEXT(A2,"格式代码")

关键是格式代码那部分。我整理了一个对照表，你把A2换成自己的日期单元格就能直接用：

实际操作中我最常用的是aaaa，因为“星期一”这种全称在任何场景都不会显得草率。如果你想要紧凑一点，比如图表坐标轴上的刻度标签，用aaa返回“周一”会更清爽。

2.2 中英文区域下的差异

这里必须提醒一个容易踩的坑：ddd和dddd在中文版Excel里返回的一律是英文，比如Mon、Monday。很多新人拿=TEXT(A2,"ddd")想得到“周三”，结果出来一个“Wed”，当场懵掉。这是因为ddd/dddd本质是英文日期格式代码，而中文的星期缩写和全称要用aaa/aaaa，这是两套独立代码，不能混着用。

如果你确实需要用英文缩写，但又想首字母大写，可以写成=TEXT(A2,"ddd")，然后在单元格格式里设置小大写或者直接加个UPPER函数。不过说实话，我一般不会这么做，英文报表直接用dddd就行，没必要过度加工。

2.3 注意：TEXT返回的是文本，不是日期

TEXT函数有个容易被忽视的副作用：它返回的是字符串，不是日期。这意味着你拿这个结果去做排序、比较、透视表分组的时候，它不会按照星期一到星期日的自然顺序排，而是按文本排序：星期三、星期二、星期一这种七扭八歪的顺序。

所以我的建议是，TEXT适合“一次性展示”，比如生成报告标题、拼接备注文本，就不太适合作为统计分析用的辅助列。如果你需要用这个星期列做排序或者作为透视表的行标签，那就老老实实生成数字型的星期列，或者用接下来讲到的WEEKDAY方案。想拼接一句话的时候，TEXT反而特别好用，比如="本周五："&TEXT(TODAY(),"aaaa")，结果直接是“本周五：星期五”，做报表标题很实用。

3. 用WEEKDAY函数配CHOOSE，把数字翻译成中文星期

3.1 WEEKDAY的返回值类型决定一切

WEEKDAY函数本身不返回中文星期，它只返回一个数字，关键是这个数字代表星期几完全取决于第二个参数return_type。这个参数是无数人翻车的重灾区，我把最常见的类型列出来：

默认的return_type=1把星期日当作一周的第一天，所以星期日返回1，星期一返回2。很多中国用户习惯一周从周一开始，那就要明确写成WEEKDAY(A2,2)，让周一返回1，周日返回7。别小看这个差异，我见过不止一次报表里因为默认类型导致所有星期都错位一天，排查半天才发现是return_type写错了。

3.2 经典嵌套公式：CHOOSE配WEEKDAY

既然WEEKDAY已经返回1到7的数字了，接下来用CHOOSE函数把数字映射成中文文字就行了。假如你要输出“星期一”这种全称，公式这样写：

=CHOOSE(WEEKDAY(A2,2),"星期一","星期二","星期三","星期四","星期五","星期六","星期日")

这里CHOOSE的第一个参数是序号，也就是WEEKDAY返回的数字，后面的“星期一”到“星期日”按顺序对应1到7。因为WEEKDAY的第二个参数用的2，所以序号1对应周一，序号7对应周日，逻辑非常顺。

如果你想要简称“周一”这种，把后面的内容改成"周一","周二","周三","周四","周五","周六","周日"就行。

3.3 进阶：把映射表放到辅助区域，公式更好维护

CHOOSE方案虽然直观，但公式里堆一堆中文，如果后面要改说法，你得挨个公式去改。更工程化的做法是把星期文本放到一个辅助区域，再用INDEX按序号取。比如在G1到G7单元格分别输入“星期一”到“星期日”，公式写成：

=INDEX($G$1:$G$7,WEEKDAY(A2,2))

这样以后想改成“礼拜一”，只需要改G1到G7里的内容，所有公式结果自动更新。这个方法特别适合做模板，别人接手你的表格时不用扒公式，改辅助区就行。

WEEKDAY+CHOOSE/INDEX的方案还有一个隐藏优势：它不依赖于系统语言和区域设置，不管你的Excel是中文版、英文版还是日语版，输出内容完全由你自己控制。做跨团队共享报表的时候，这一点比TEXT格式代码更稳。

4. 用MOD手写星期算法，看清底层原理

4.1 为什么序列数除以7的余数和星期有关

这一节有点硬核，但我觉得值得讲，因为理解了原理之后，很多“奇怪”的Excel行为你都能自己判断了。前面说过，日期在Excel里就是序列数，既然是序列数，那它就具备周期性——每增加7天，星期就轮回一次。

所以理论上，只要我们把日期序列数对7取余数，就能得到一个对应星期几的数字。以Windows默认的1900日期系统为例，Excel内部序列数的基准是1900年1月1日，这个日期在Excel里显示为星期日（这是历史遗留兼容问题，别拿现实日历去硬算，会越算越乱）。因此，我们基于Excel自己的显示去校准：

• 序列数1，Excel显示为星期日
• 序列数2，显示为星期一
• 序列数3，显示为星期二
• 序列数7，显示为星期六
• 序列数8，又回到星期日

转换成公式就是MOD(A2-1,7)+1，结果落在1到7之间，其中1对应星期日，2对应星期一，7对应星期六。

4.2 用CHOOSE把余数映射成中文

有了上面的数字映射关系，直接用CHOOSE生成本文方案：

=CHOOSE(MOD(A2-1,7)+1,"星期日","星期一","星期二","星期三","星期四","星期五","星期六")

我在4.1里特意强调是用Excel自己的显示来校准，因为如果你按真实日历推，会发现1900年1月1日其实是星期一，不是Excel显示的星期日。这个差异来自Excel为了兼容老软件而强制假定了1900年是闰年，属于历史遗留bug。我们做应用层的人没必要纠结这个bug本身，只要记住：从序列数推星期的时候，一切以Excel的WEEKDAY和TEXT输出为基准，不要自己另起炉灶。

4.3 1904日期系统下怎么微调

如果你打开Excel文件属性，发现勾选了“使用1904日期系统”，那么同样的MOD公式结果会整体偏移。最简单的做法是换回WEEKDAY方案，因为它内部已经处理了日期系统的差异。如果非要用MOD，可以在公式里减去1462天的偏移，但说实话没必要，方案又不是只有这一个，能用现成函数就别手算偏移量。

4.4 这个方案的实战价值

你可能会问，既然有WEEKDAY和TEXT，为什么还要学MOD？我的感受是：MOD在条件格式和“是否周末”判断里非常好用。比如你想把周末行标红，不需要得出中文星期，只要判断序列数落在哪个范围：

=OR(MOD(A2-1,7)+1=1,MOD(A2-1,7)+1=7)

这个公式直接判断是不是周日或周六，用在条件格式里干净利落，不用套一层中文映射再判断。理解MOD原理之后，这类星期相关的逻辑判断你都能自己写出来，不会被函数名吓住。

5. 自定义单元格格式，不改变数据只改变显示

5.1 三步设置，日期还是那个日期

如果你只是想在报表里让人看到“星期一”，但又不希望真的把日期改成文本，最佳方案是自定义单元格格式。操作非常简单：

1. 选中日期列或区域。
2. 按Ctrl+1打开设置单元格格式。
3. 在“数字”选项卡里选“自定义”，类型输入aaaa或aaa，确定。

就这么简单。输入aaaa显示“星期一”，输入aaa显示“周一”。你会发现单元格显示变了，但你点开这个单元格，编辑栏里依然是原来的日期序列数，比如2024/1/15。这个方案的本质只是换了层皮，底层数据纹丝不动。

5.2 格式代码的写法细节

自定义格式里可用的星期代码和TEXT函数基本一致：

这里我要多说一句：很多人习惯直接在“日期”分类里挑现成格式，但那里通常没有纯星期的选项。你需要切到“自定义”手动输入aaaa，才能得到纯粹的“星期三”显示，不带年月日。

5.3 为什么做报表时我优先用这个方案

拿我自己做周报的经验来说，自定义格式是最“不污染数据”的方案。它有三个实打实的好处：

第一，日期还是日期，后续如果要做趋势分析、日期筛选，完全不受影响，你只是换了显示方式而已。第二，数据透视表里直接把日期字段拉进来，再自定义格式，就能实现按星期展示，不需要额外加辅助列。第三，不会因为TEXT公式生成了一堆文本而增加文件体积，也不容易因为有人误删公式而出错。

代价也有：如果你把这个区域复制到别的文件，对方可能看不到预设格式，看到的是原始日期。另外，如果别人拿到文件后用公式引用这个单元格，拿到的仍然是日期序列数，不是“星期三”这个文本。所以这个方案适合“看”的场景，不适合“取”的场景。

顺带说一句，如果你遇到“日期设置斜杠显示不出来”这种问题，也多半和自定义格式有关——单元格格式被改成了文本或别的格式，手动设置成yyyy/m/d或yyyy-mm-dd就能恢复。格式这件事，Excel里真的是“所见非所得”的重灾区。

6. 快速填充Ctrl+E，没有公式也能批量转

6.1 操作步骤：两行示范+回车

如果你用的Excel版本是2013以上，其实还有一个更“懒”的方案：快速填充，快捷键Ctrl+E。它的逻辑是你给Excel做个示范，它自己猜规律并往下填。

假设A列是日期，你想在B列得到对应的中文星期，可以这样操作：

1. 在B2手动输入“星期一”——以A2这个日期实际对应的星期为准。
2. 在B3手动输入“星期二”——如果A3刚好是周二，那就完美。
3. 选中B4或B3，按Ctrl+E。

Excel会按照前两行的规律，自动把A列剩余日期全部转换成星期文本。注意，手动输入的两个示例最好覆盖两个不同的星期，这样Excel才能正确识别“日期→星期”的映射规律，而不是误以为你在做等差填充。

这个方案不只适用于纯星期转换。比如你想生成“2024年1月15日 星期一”这种完整标签，先在B2输入一个完整示例，同样按Ctrl+E，它也能批量生成，相当于零公式完成了一次TEXT拼接。

6.2 实测下来的边界：什么时候会翻车

快速填充看着很智能，实际用起来还是有条件的。我的实测结论是：数据规律越规整，成功率越高；数据越混乱，它越容易“自由发挥”。下面几种情况我遇到过头疼的问题：

• 日期列混合了“2024/1/15”“2024-01-15”“2024年1月15日”多种写法，Excel找不到统一规律，结果会错乱。
• 日期里带了时间，比如“2024/1/15 14:30”，快速填充容易把时间也带进去或截断。
• 源数据是文本日期且格式混乱，它可能原样照抄，而不是解析成星期。
• 中间有空白单元格，它会断掉，空行之后需要重新按一次Ctrl+E。

所以我的建议是：快速填充适合一次性、小规模、格式整齐的数据，不适合当作正式数据处理的常规手段。用完之后最好立即把结果粘贴成值，防止后续因为源数据变动导致填充结果“塌方”——快速填充的结果是静态文本，源数据改了它不会自动更新，这点要想清楚。

6.3 隐藏技巧：先用TEXT做标准示范，再用Ctrl+E

如果你确实想用Ctrl+E，又怕它猜错，我有一个土办法：先在辅助列用TEXT公式生成一列标准星期文本，然后把这列复制成值，再作为示范给快速填充“抄作业”。相当于你先把正确答案放在它面前，让它按这个规律去处理非标准的数据列。这种组合能减少很多“瞎猜”的失败率。

不过说实话，如果数据量已经大到需要依赖快速填充来提速，我更推荐直接上函数或Power Query，至少在规律复杂时函数是可控的、可复现的。

7. 大数据量场景：Power Query和VBA各有各的解法

7.1 Power Query里转换星期的两种方式

当数据量到了几万行、几十万行，或者你这个报表每周都要刷新一次时，手写公式和快速填充都不够优雅。这时候把数据装进Power Query，处理步骤会被记录下来，下次一键刷新。

最简单的操作路径是：选中数据区域→数据→自表格/区域，进入Power Query编辑器。然后在“添加列”菜单里找“日期”→“星期”，这里会有“星期名称”之类的选项，点击后会自动生成一列英文或本地化的星期名称。若是中文环境，有时直接就是“星期一”这种格式，但如果你发现生成的是英文Monday，也不要慌，可以用自定义列写M公式来做映射。M函数里有个Date.DayOfWeek，返回0到6之间的数字，数字起点可以在第二个参数里用Day.Monday或Day.Sunday指定。

我会建议在Power Query里生成数字型的星期列，然后做条件列映射成中文。虽然多一步，但逻辑更透明，也不容易受系统语言影响。这属于典型的“用一次别扭，之后所有刷新都顺畅”的方案。

还有一点值得说：Power Query处理日期转星期的最大优势，不是速度快，而是“可重复”。你把清洗步骤保存下来，下次新数据来了，右键刷新一下，所有处理自动重跑一遍，跟你第一次设定的规则完全一致。这种自动化能力对做周报、月报的人来说，省下的时间不是一点点。

7.2 VBA宏批量转换与Format函数

如果你的Excel版本没有Power Query，或者你更习惯轻量级自动化，用VBA写个几行的小宏也完全可以。下面这个宏会把A列日期对应的星期写到B列，用的是VBA的Format函数，和Excel的TEXT类似：

Sub DateToWeekday() Dim rng As Range Dim c As Range Set rng = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row) For Each c In rng If IsDate(c.Value) Then c.Offset(0, 1).Value = Format$(c.Value, "aaaa") End If Next c End Sub

这段代码会自动识别A列的有效数据范围，逐行判断单元格是不是日期，是日期才转成星期写入B列。LocalId说明一下，Format$(c.Value, "aaaa")在中文版Excel里返回“星期一”这种全称，如果你的Office是英文版，可能需要把格式代码换成dddd或者调整系统区域设置，这也是VBA方案的坑之一。

如果不想写成静态值，而是希望在B列保留公式，可以把赋值语句改成：

c.Offset(0, 1).Formula = "=TEXT(A" & c.Row & ",""aaaa"")"

这样B列生成的是公式，源数据变动时结果会跟着更新。不过公式多了在大表格里会拖慢打开速度，我一般会建议直接转成值保存，除非你确实需要动态联动。

7.3 什么时候才需要自动化

被问到最多的问题是：“我到底该不该用Power Query或VBA？”我的判断标准其实很朴素：

• 数据量小，一次性处理，直接函数或手输都行。
• 数据量大，但只处理一次，用公式拖下来也够用，最多卡几秒。
• 数据量大，而且这个表每周/每月都要重复处理，才值得上Power Query或VBA。

自动化不是为了炫技，是为了减少重复劳动。如果你一个月只做一次的小表也要上Power Query，那配置步骤的时间可能比手动处理还长，得不偿失。

8. 六种方法横向对比与我的选型心得

8.1 一张表看完复杂度、区域依赖、适用场景

把六种方案放一起对比，选型就非常清晰了：

这张表只看两个维度：结果是要“显示”还是“取值”，以及数据是要“一次性”还是“周期性”。这两个维度想清楚了，选型基本不会错。

8.2 最容易踩的坑：周起始日和日期系统

前面分散提到了不少坑，这里我再把最高频的三个集中说一遍：

第一个是WEEKDAY的return_type忘记写。默认值1表示周日开始，很多中国人会下意识认为1=周一，结果所有星期错位一天。我建议不管用不用WEEKDAY，只要牵扯到星期判断，一律显式写明第二个参数。

第二个是1900和1904日期系统混用。同一个文件在不同电脑上打开，如果有一方勾选了1904系统，日期序列数会差1462天。文件在团队里流转时，这种隐性差异很容易引发“公式结果不对”的投诉。最稳妥的办法是团队统一Excel设置，或者在公式层面避免依赖序列数的绝对位置。

第三个是文本日期。不管用哪个函数，源数据是文本就一切白搭。遇到这种情况，优先用分列功能：选中日期列→数据→分列→下一步直到第三步，列数据格式选“日期”，一步把文本日期转成真日期。如果你发现VLOOKUP同样的日期一列能匹配一列匹配不上，原因通常也是两列里有一列是文本，有一列是日期。

8.3 我个人在实际操作中的体会

做表格这么多年，我现在处理“日期转星期”基本形成了条件反射：如果是给别人看的报表，优先用自定义格式，因为不动源数据，日期保留，后续怎么折腾都方便；如果需要一列真实的星期文本用于筛选、透视，我会用WEEKDAY+INDEX配合辅助区，公式短、好维护；如果只是临时拼个标题或者发个通知，TEXT函数一把梭，快就完事了。

还有一个小习惯想分享：遇到要标记周末的场景，我不会先去转成中文星期再判断，而是直接判断MOD序列数的结果，因为无论你怎么显示，底层的周期规律是固定的，直接用数字判断最省事。

日期转星期在Excel里是一个非常小的需求，但它牵涉到的底层知识点不少：日期序列数、文本与日期的区别、区域格式、日期系统。把这六种方法都试一遍，你会发现不仅这个需求解决了，以后再遇到类似的时间处理问题，你也会更有底气。