先说个常见场景你手里是一张销售明细表日期列从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换成自己的日期单元格就能直接用格式代码返回结果适用场景dddMon英文缩写适合英文报表ddddMonday英文全称适合外企邮件aaa周一中文简称适合表格列标题aaaa星期一中文全称适合对外正式报表实际操作中我最常用的是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或省略23456712123456730123456111234567172345671默认的return_type1把星期日当作一周的第一天所以星期日返回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里的内容所有公式结果自动更新。这个方法特别适合做模板别人接手你的表格时不用扒公式改辅助区就行。WEEKDAYCHOOSE/INDEX的方案还有一个隐藏优势它不依赖于系统语言和区域设置不管你的Excel是中文版、英文版还是日语版输出内容完全由你自己控制。做跨团队共享报表的时候这一点比TEXT格式代码更稳。4. 用MOD手写星期算法看清底层原理4.1 为什么序列数除以7的余数和星期有关这一节有点硬核但我觉得值得讲因为理解了原理之后很多“奇怪”的Excel行为你都能自己判断了。前面说过日期在Excel里就是序列数既然是序列数那它就具备周期性——每增加7天星期就轮回一次。所以理论上只要我们把日期序列数对7取余数就能得到一个对应星期几的数字。以Windows默认的1900日期系统为例Excel内部序列数的基准是1900年1月1日这个日期在Excel里显示为星期日这是历史遗留兼容问题别拿现实日历去硬算会越算越乱。因此我们基于Excel自己的显示去校准序列数1Excel显示为星期日序列数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)11,MOD(A2-1,7)17)这个公式直接判断是不是周日或周六用在条件格式里干净利落不用套一层中文映射再判断。理解MOD原理之后这类星期相关的逻辑判断你都能自己写出来不会被函数名吓住。5. 自定义单元格格式不改变数据只改变显示5.1 三步设置日期还是那个日期如果你只是想在报表里让人看到“星期一”但又不希望真的把日期改成文本最佳方案是自定义单元格格式。操作非常简单选中日期列或区域。按Ctrl1打开设置单元格格式。在“数字”选项卡里选“自定义”类型输入aaaa或aaa确定。就这么简单。输入aaaa显示“星期一”输入aaa显示“周一”。你会发现单元格显示变了但你点开这个单元格编辑栏里依然是原来的日期序列数比如2024/1/15。这个方案的本质只是换了层皮底层数据纹丝不动。5.2 格式代码的写法细节自定义格式里可用的星期代码和TEXT函数基本一致格式代码显示效果底层数据aaaa星期三仍是日期aaa周三仍是日期ddddWednesday仍是日期dddWed仍是日期这里我要多说一句很多人习惯直接在“日期”分类里挑现成格式但那里通常没有纯星期的选项。你需要切到“自定义”手动输入aaaa才能得到纯粹的“星期三”显示不带年月日。5.3 为什么做报表时我优先用这个方案拿我自己做周报的经验来说自定义格式是最“不污染数据”的方案。它有三个实打实的好处第一日期还是日期后续如果要做趋势分析、日期筛选完全不受影响你只是换了显示方式而已。第二数据透视表里直接把日期字段拉进来再自定义格式就能实现按星期展示不需要额外加辅助列。第三不会因为TEXT公式生成了一堆文本而增加文件体积也不容易因为有人误删公式而出错。代价也有如果你把这个区域复制到别的文件对方可能看不到预设格式看到的是原始日期。另外如果别人拿到文件后用公式引用这个单元格拿到的仍然是日期序列数不是“星期三”这个文本。所以这个方案适合“看”的场景不适合“取”的场景。顺带说一句如果你遇到“日期设置斜杠显示不出来”这种问题也多半和自定义格式有关——单元格格式被改成了文本或别的格式手动设置成yyyy/m/d或yyyy-mm-dd就能恢复。格式这件事Excel里真的是“所见非所得”的重灾区。6. 快速填充CtrlE没有公式也能批量转6.1 操作步骤两行示范回车如果你用的Excel版本是2013以上其实还有一个更“懒”的方案快速填充快捷键CtrlE。它的逻辑是你给Excel做个示范它自己猜规律并往下填。假设A列是日期你想在B列得到对应的中文星期可以这样操作在B2手动输入“星期一”——以A2这个日期实际对应的星期为准。在B3手动输入“星期二”——如果A3刚好是周二那就完美。选中B4或B3按CtrlE。Excel会按照前两行的规律自动把A列剩余日期全部转换成星期文本。注意手动输入的两个示例最好覆盖两个不同的星期这样Excel才能正确识别“日期→星期”的映射规律而不是误以为你在做等差填充。这个方案不只适用于纯星期转换。比如你想生成“2024年1月15日 星期一”这种完整标签先在B2输入一个完整示例同样按CtrlE它也能批量生成相当于零公式完成了一次TEXT拼接。6.2 实测下来的边界什么时候会翻车快速填充看着很智能实际用起来还是有条件的。我的实测结论是数据规律越规整成功率越高数据越混乱它越容易“自由发挥”。下面几种情况我遇到过头疼的问题日期列混合了“2024/1/15”“2024-01-15”“2024年1月15日”多种写法Excel找不到统一规律结果会错乱。日期里带了时间比如“2024/1/15 14:30”快速填充容易把时间也带进去或截断。源数据是文本日期且格式混乱它可能原样照抄而不是解析成星期。中间有空白单元格它会断掉空行之后需要重新按一次CtrlE。所以我的建议是快速填充适合一次性、小规模、格式整齐的数据不适合当作正式数据处理的常规手段。用完之后最好立即把结果粘贴成值防止后续因为源数据变动导致填充结果“塌方”——快速填充的结果是静态文本源数据改了它不会自动更新这点要想清楚。6.3 隐藏技巧先用TEXT做标准示范再用CtrlE如果你确实想用CtrlE又怕它猜错我有一个土办法先在辅助列用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 一张表看完复杂度、区域依赖、适用场景把六种方案放一起对比选型就非常清晰了方法是否保留日期类型是否受区域设置影响适合场景我的推荐指数TEXT函数否结果是文本受中文代码与英文代码不同一次性生成星期文本、拼接标题高WEEKDAY CHOOSE/INDEX否结果是文本不受完全自控统计分析辅助列、跨语言环境高MOD CHOOSE否结果是文本受1900/1904日期系统影响条件格式、理解日期原理中自定义单元格格式是日期保留受格式代码随语言变报表展示、透视表按星期看很高快速填充CtrlE否结果是文本不受函数影响但受文本规律影响小规模一次性转换、拼完整标签中低Power Query / VBA视写法而定受M函数和Format需适配大数据量、周期性重复数据处理高这张表只看两个维度结果是要“显示”还是“取值”以及数据是要“一次性”还是“周期性”。这两个维度想清楚了选型基本不会错。8.2 最容易踩的坑周起始日和日期系统前面分散提到了不少坑这里我再把最高频的三个集中说一遍第一个是WEEKDAY的return_type忘记写。默认值1表示周日开始很多中国人会下意识认为1周一结果所有星期错位一天。我建议不管用不用WEEKDAY只要牵扯到星期判断一律显式写明第二个参数。第二个是1900和1904日期系统混用。同一个文件在不同电脑上打开如果有一方勾选了1904系统日期序列数会差1462天。文件在团队里流转时这种隐性差异很容易引发“公式结果不对”的投诉。最稳妥的办法是团队统一Excel设置或者在公式层面避免依赖序列数的绝对位置。第三个是文本日期。不管用哪个函数源数据是文本就一切白搭。遇到这种情况优先用分列功能选中日期列→数据→分列→下一步直到第三步列数据格式选“日期”一步把文本日期转成真日期。如果你发现VLOOKUP同样的日期一列能匹配一列匹配不上原因通常也是两列里有一列是文本有一列是日期。8.3 我个人在实际操作中的体会做表格这么多年我现在处理“日期转星期”基本形成了条件反射如果是给别人看的报表优先用自定义格式因为不动源数据日期保留后续怎么折腾都方便如果需要一列真实的星期文本用于筛选、透视我会用WEEKDAYINDEX配合辅助区公式短、好维护如果只是临时拼个标题或者发个通知TEXT函数一把梭快就完事了。还有一个小习惯想分享遇到要标记周末的场景我不会先去转成中文星期再判断而是直接判断MOD序列数的结果因为无论你怎么显示底层的周期规律是固定的直接用数字判断最省事。日期转星期在Excel里是一个非常小的需求但它牵涉到的底层知识点不少日期序列数、文本与日期的区别、区域格式、日期系统。把这六种方法都试一遍你会发现不仅这个需求解决了以后再遇到类似的时间处理问题你也会更有底气。