很早就想写一篇关于Excel日期函数的东西。起因是前阵子帮同事修一张考勤表她在每个打卡记录后面都写了NOW()想留个时间戳结果第二天打开文件所有“提交时间”全变了最后核算时整张表废掉重来。这事折腾了半天根因就是没搞懂TODAY、NOW这类函数到底是什么脾气。后来我养成了一个习惯但凡表格里要用日期函数先把“日期在Excel里是数字”这件事跟人讲明白再讲函数几乎不会再出那种看起来特别离谱的错。这篇就围绕TODAY与NOW这两个入口把提取年、月、日、星期这一整套常见玩法连同我平时踩过的坑一起梳理出来。适合做报表、管台账、排班、跟合同到期的人参考哪怕你只会在Excel里填数看完也能直接套用。1. 先弄明白日期在Excel里到底是怎么存的1.1 日期本质是一串带小数的数字很多人第一次看到日期变成“45658”这种数字时第一反应是表格坏了。其实没有坏Excel内部从来就不存“2025年1月1日”这种文本它存的是一个序列号。1900年1月1日被记为1往后每过一天数字加1所以2025年1月1日对应的序列号就是45658。这个数字可以带小数小数部分就是一天内的时间。比如45658.5整数部分是日期0.5代表半天也就是12点整。理解了这一点很多现象就能解释通了为什么同一天输入完有的格子右下角绿三角提示“文本”有的显示一串数字本质都是它没有被正确识别成日期或者底层存储和显示格式对不上。我在实操中判断一个格子是不是“真日期”从来不看它显示成什么样而是选中单元格看编辑栏左侧的那个框。如果显示的是“2025/1/1”这种带斜杠的格式它就是日期类型如果显示的是45658这种数字说明它是数字但被设置了日期显示格式如果显示的是你输入的原文“2025.1.1”且左对齐那基本就是文本后续所有计算都会出问题。1.2 1900系统、闰年Bug和序列号的由来Excel默认用1900日期系统也就是说1代表1900年1月1日。这个系统里有个著名的历史遗留BugExcel认为1900年2月29日是存在的而1900年实际不是闰年。这个Bug来自当年兼容电子表格软件的老传统属实用不上也绕不开但它带来的实际影响很小普通业务场景根本不会触及那几天。反而真正需要记住的是日期计算加减多少天本质上就是序列号加减多少。好比2025年1月1日加7天等于45658加7结果是45665对应2025年1月8日。我们心里有数后再遇到“日期加天数”这类问题不用老纠结“为什么Excel能把日期相加”它背后就是算术题。另外提一句如果想快速看某个日期的序列号可以临时把单元格格式改成“常规”数字立刻露出来。看完记得改回去不然同事收到一个纯数字日期会以为表坏了。1.3 TODAY与NOW谁在控制这些时间函数TODAY和NOW没有任何参数写法是TODAY()和NOW()括号里必须为空。TODAY返回当前日期NOW返回当前日期加时间。如果从底层看TODAY()返回的其实就是当前序列号的整数部分NOW()返回带小数的完整序列号。简单记法TODAY是日期版NOW是时间戳版。我用这两者的经验是凡是只需要“今天几号”的地方比如判断今天有没有到期、今天属于这个月的第几天一律用TODAY凡是需要记录“现在是几点几分”的地方比如登记操作时间、计算两个时刻之间的时长才用NOW。不要上来不管三七二十一就写NOW看起来比TODAY“高级”实际带来很多不必要的计算结果变动。这里放一张对比表方便大家一眼看清区别对比项TODAY()NOW()返回内容当前日期当前日期和时间底层本质序列号整数部分序列号整数小数常见用途到期判断、按日统计时间戳、工时计算刷新频率工作表重算时更新工作表重算时更新典型坑文件隔天打开日期就变文件隔天打开时间也全变2. TODAY与NOW的正确打开方式2.1 直接输入函数的几个细节输入TODAY()时函数名不需要区分大小写但括号必须是英文半角。中文输入法全角状态下输入的括号Excel不认最常见的报错就是#NAME?。遇到这个错误提示先看公式里的括号是不是全角十次有八次是这个问题。TODAY和NOW另一个特点是它们会跟随系统时钟更新。你昨天打开文件时如果结果是1月5日今天再打开不用动任何键就变成1月6日了。有时候这是好事报表每天自动更新有时候却是灾难比如领导要看的是“截至1月5日的数”你1月6日打开打印所有日期全变了。要固定住当前日期我推荐两个快捷键Ctrl;输入当前日期CtrlShift;输入当前时间效果是静态文本不会自动变。如果单元格里已经有公式又需要固化成值就复制那列右键选择性粘贴选择“值”。这是我在做月报时必做的一步能解决九成“日期对不上”的纠纷。2.2 动态时间戳与报表固定时间的取舍日常办公里经常要做“更新时间”字段比如在某个单元格写NOW()告诉别人这个表是什么时候刷新的。第一眼看上去很方便但问题马上出现只要这个工作薄里的任意单元格发生变化NOW就会重算刷新时间会自己变。比如你在旁边改了一个错别字再敲回车“更新时间”就从上午9点跳到了9点03分可你根本没有刷新数据。所以时间戳类的字段我现在的习惯是要么用快捷键输入静态时间要么保留公式但只在真正要出报表的时候手动F9重算一次导出前再粘贴成值。如果确实需要动态时间戳不想每次手动操作可以用数据验证加公式的思路绕一下。但多数业务表没这么高的并发需求老老实实粘贴值最稳。别小看这个细节我在统计日报的时候就吃过亏日报里放了NOW其他同事顺手改了格式整表重算最后上报的“数据更新时间”比我实际跑数时间晚了半小时解释起来特别费劲。2.3 理解“易失性函数”避免工作簿被拖垮TODAY和NOW属于易失性函数意思是它们没有稳定的计算缓存只要Excel发生重算它们就重新取一次系统时间。理想状态下这是“自动更新”但代价是性能一个几千行的大表里如果塞了几百个NOW每次敲单元格、筛选、排序Excel都得重算这一大片卡顿是必然的。我做数据清洗时见过最离谱的表里面有2000多列公式记录每条数据的落库时间全是NOW。后果就是任何一次编辑整张表要转大概十秒圈。后来我把那一整块全部替换成静态时间体积也小了打开速度也回来了。如果你的场景注定要用多个易失函数建议把计算选项从“自动”改成“手动”需要更新时按F9统一重算。路径在“公式”选项卡里的“计算选项”下拉菜单里设置。改完记得告诉用这张表的人别傻等想刷新按F9。这种方案适合大型报表小表格没必要。3. 提取年月日星期数的函数工具箱3.1 YEAR、MONTH、DAY最稳的三件套提取日期里的年、月、日第一反应永远是YEAR()、MONTH()、DAY()这三个函数。它们的参数只有一个写个日期单元格引用或日期函数都行。假设A2是2025年3月8日那么YEAR(A2)返回2025MONTH(A2)返回3DAY(A2)返回8这三个函数返回的是数值可以直接参与加减比较比如判断是不是某一年IF(YEAR(A2)2025,是,否)。也可以用来做辅助列比如把每天的订单按“年-月”拼接成一个分组字段YEAR(A2)-MONTH(A2)。不过这里我建议一个习惯如果要拼接文本优先用TEXT函数后面细说因为MONTH返回的是数字不会自动补前导零。比如3月会得到“3”想得到“03”就得自己写IF(MONTH(A2)10,0MONTH(A2),MONTH(A2))很啰嗦。3.2 WEEKDAY和星期数字返回值类型才是关键提取“星期几”有两类做法一类要数字一类要文本。要数字时用WEEKDAY(日期,返回类型)。第二个参数return_type是坑最多的点。不写默认为1这时1代表周日2代表周一……7代表周六。很多人以为星期一是1直接写WEEKDAY(A2)判断结果发现星期一那天返回2逻辑全错。我用得最多的是第二参数写2因为它的规则符合国内习惯周一1周二2……周日7。这样判断工作日和周末非常方便IF(WEEKDAY(A2,2)5,工作日,周末)第三参数3也有用它让周一0到周日6适合某些排班系统日常反而少。return_type返回规则适合场景省略或1周日1周六7欧美习惯的周日起始日历2周一1周日7国内习惯、上班族判断3周一0周日6固定索引、数据建模顺便说一下WEEKNUM和ISOWEEKNUM。WEEKNUM返回一年中的第几周第二参数控制每周从周日还是周一开始ISOWEEKNUM按ISO标准返回周数它在跨年时更严谨。排产、周报分周统计会用到新手知道有这东西就行需要用的时候查一下参数即可。3.3 TEXT函数把日期变成任何你想要的文本格式如果你想要“2025年3月8日”这种带中文的完整日期或者“周六”这种中文星期TEXT是最快的。常见写法如下TEXT(A2,yyyy年mm月dd日)得到2025年03月08日TEXT(A2,yyyy年m月d日)得到2025年3月8日TEXT(A2,aaaa)得到星期六TEXT(A2,aaa)得到周六TEXT(A2,dddd)得到Saturday这里有个重要提醒TEXT返回的是文本不是日期。哪怕它看起来和日期一模一样也不能直接做加减运算。如果后续还要算天数、做透视表日期分组保留原始日期列把TEXT结果做辅助列就行别用TEXT结果覆盖原值。还有一个容易混淆的点格式代码里的“m”。它在日期场景里代表月份在时间场景里代表分钟。比如TEXT(A2,h:mm)里的mm是分钟TEXT(A2,mm)在没有小时的情况下是月份。所以看别人公式时别一看到mm就以为是月要结合上下文。3.4 不想写公式分列和自定义格式也能提取有时候你只是想“看”到年和月不想生成一列公式其实自定义格式就够了。选中日期列右键设置单元格格式在“自定义”里输入yyyy年m月d日显示效果就和TEXT一样但底层依然是日期序列号还能继续计算。这种情况适合不改结构、只改显示的报表。另一个更实用的功能是分列。如果有一列脏日期比如“2025.3.8”“20250308”“08032025”混着来直接写公式会很痛苦。这时选中这一列数据选项卡里点“分列”前两步不用改第三步在“列数据格式”选“日期”再选一个能读懂的格式Excel就会尽力把它转成标准日期。分列解决“文本格式日期”的效率很高我清理外部系统导出的数据时常用这一招。4. 实战让日期函数自动跑起来的几个场景4.1 统计本月、本周的销售或业务数据动态统计的第一步是构造“日期边界”。比如要统计本月截至今天的销售额可以用SUMIFS(销售额列, 日期列, DATE(YEAR(TODAY()),MONTH(TODAY()),1), 日期列, TODAY())这里的DATE(YEAR(TODAY()),MONTH(TODAY()),1)返回本月1号TODAY()作为上界两条条件夹住整个区间。这个方法比手工写死“2025-03-01”强多了因为下个月打开文件它能自动变成4月1日。如果要做本周统计需要知道本周一的日期。公式是TODAY()-WEEKDAY(TODAY(),2)1再往下推上周一的日期是TODAY()-WEEKDAY(TODAY(),2)1-7上周日则是TODAY()-WEEKDAY(TODAY(),2)这里不要求你背下每个公式关键是理解WEEKDAY返回的“星期几数值”能把今天拉回到周一。理解了之后什么“本周日”“上周日”“下周一”都能自己推导不用死记。实际跑数时我习惯先把这些日期边界分别放在几个单元格里比如B1放本月1号、B2放本周一、B3放上周一然后用SUMIFS引用这些单元格。这样公式看起来简洁调试也方便。4.2 自动计算年龄、工龄和剩余到期天数算年龄最标准的是DATEDIF虽然它不在函数库里显示但一直能用DATEDIF(出生日期, TODAY(), Y)返回整岁数这个函数可返回多种差异单位参数含义示例结果Y完整年数38YM忽略年剩余月数5MD忽略年、月剩余天数12D总天数14004拼接“X年X个月X天”工龄的公式是DATEDIF(入职日, TODAY(), Y)年DATEDIF(入职日, TODAY(), YM)个月DATEDIF(入职日, TODAY(), MD)天计算距离下次生日还剩多少天可以先进一步DATE(YEAR(TODAY()), MONTH(生日), DAY(生日))这个公式能算出“今年生日是哪一天”。如果这个日期已经过了说明生日在今年已经结束那就得再加一年。为了处理2月29日这种特殊情况我建议用EDATE辅助而不是手动往YEAR后面加1因为平年的2月29日会被自动折算成3月1日。公式可以这样写IF(今年生日TODAY(), 今年生日365, 今年生日)-TODAY()虽然2月29日这种生日在平年会差一天但多数场景没人计较到这一步。4.3 到期提醒与条件格式联动给合同、证书、保质期做到期提醒逻辑很简单用截止日期减TODAY得到剩余天数然后再加个条件格式让临近到期的行变色。剩余天数公式截止日期-TODAY()如果显示负数说明已经过期了。接着选中整个数据区域用“开始-条件格式-新建规则-使用公式确定要设置格式的单元格”输入AND($A2-TODAY()0, $A2-TODAY()30)这个公式判断A列日期在今天之后30天内命中就标黄。再添加一条规则$A2-TODAY()0命中就标红表示已过期。注意公式里的$A2要和你选中区域的首行对应具体引用哪个单元格以实际列为准。条件格式里出现日期变数字不用慌因为条件格式判断的本质就是序列号比较数字显示只是编辑框的正常现象重点看规则返回的TRUE/FALSE对不对。4.4 考勤表里判断上下午和星期考勤和排班是日期函数的另一个高频场景。“上下午”判断用时间来切分最方便IF(NOW()-TODAY()0.5, 上午, 下午)为什么是0.5因为一天被记为10.5对应中午12点。“今天是否工作日”判断IF(WEEKDAY(TODAY(),2)5, 上班, 休息)排班时如果需要按星期值班也可以用TEXT(日期, aaa)把周一这种简写星期直接显示出来再配合第二个辅助列排班比人工翻日历快得多。如果公司是大小周或者把法定节假日也算休息那就要引入节假日清单表再用NETWORKDAYS系列函数去排除休息日这条后面单独展开。5. 常见问题和排查技巧实录5.1 日期变成数字怎么办这是最经典的问题。单元格显示45658不显示2025-01-01通常是两种情况单元格格式被设成了“常规”或者从外部导入时丢了格式。解决办法很简单选中列右键设置单元格格式左侧选“日期”右侧挑一种你喜欢的显示样式。底层数据完全不用改改完立刻恢复显示。如果是一大批日期被粘贴成值之后全部变成数字可以先复制原先的区域再选择性粘贴选“值和数字格式”。如果已经粘贴完只剩数字就重新设置格式别无他法。5.2 输入的日期总被识别成文本常见的脏日期形态包括“2025.1.1”“2025/1/1”“20250101”有时候单元格左上角还有绿色小三角。文本日期一多排序、透视表、加减全乱。我的处理顺序是这样选中脏日期列数据-分列前两步直接下一步第三步选“日期”完成。分列后还有字的用替换功能把点号“.”替换成“/”或“-”。纯八位数字“20250101”用公式DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))转成真日期。判断是否成功很简单选中一列看数字是否右对齐。文本日期通常左对齐真日期右对齐。这招教给了很多人非常管用。5.3 函数为什么不动日期不自动更新TODAY和NOW写好后过一天打开结果还是昨天的日期八成是计算选项被设成了“手动”。去“公式”选项卡把“计算选项”改回“自动”。如果不想改全局设置也可以按F9手动重算所有打开的工作簿ShiftF9只重算当前工作表。还有种情况公式单元格没刷新但值和格式都对只是显示没变。这时选中单元格按F2再回车或者重新设置一下单元格格式多半能刷新。另外要留意TODAY和NOW依赖系统时间操作系统的日期时间错了它们就跟着错。有些表会做“日期一致性校验”就是因为曾经有台电脑的系统时钟偏了几天导致整批到期数据提前标红。5.4 日期格式混用VLOOKUP死活匹配不上你在表1用“2025/1/1”表2用“2025-01-01 00:00:00”表面看起来都是1月1日但底层一个是没有时间的日期一个是带时刻的完整时间VLOOKUP匹配时就会翻车。这类情况我习惯先把所有日期统一成“日期”格式不带时间。如果已经带了时间可以用INT(A2)把时间部分截掉再设置成日期格式。不要靠眼睛判断“明明一样”Excel比对的是底层序列号差一个0.5都不同。排查匹配问题时我通常先做两列辅助验证一列显示序列号格式看是否完全相等一列用TEXT(A2,yyyy-mm-dd)看文本是否一致。哪个对不上就往哪个方向追。5.5 别再被1900年闰月的旧账坑到Excel默认日期系统从1900年1月1日开始且把不存在的1900年2月29日当作有效日期。这导致序列号从3月1日开始比实际天数多1天但Excel内部所有日期计算都一致所以常规业务影响微乎其微。真正需要注意的是如果公司数据库或其他系统用的是1904日期系统导入导出时日期可能整体偏移1462天。遇到日期大面积偏移时先看是不是日期系统不一致再往这个方向查否则会绕很大圈子。绝大多数个人办公表不会碰这个问题知道有这么回事就行。6. 日期函数再往前一步6.1 DATE、EOMONTH、DATEDIF的组合玩法DATE函数除了能拼日期还有一个很实用的特性参数会自动进位。比如DATE(2025,13,1)返回2026年1月1日DATE(2025,0,1)返回2024年12月1日利用这个特性计算“下个月第一天”就非常简单DATE(YEAR(TODAY()), MONTH(TODAY())1, 1)EOMONTH专门处理月末EOMONTH(TODAY(),0)返回本月最后一天EOMONTH(TODAY(),-1)1返回本月第一天。想算本月有多少天用DAY(EOMONTH(TODAY(),0))这些函数单独看都很简单组合起来威力就大了。比如算“某个月倒数第5个工作日”先拿到月末日期再往回减工作日配合后面的NETWORKDAYS会方便很多。6.2 计算工作日NETWORKDAYS与NETWORKDAYS.INTLNETWORKDAYS(开始日期,结束日期,节假日区域)计算两个日期之间的工作日天数默认周六日休息自动排除传入的节假日清单。做项目的排期评估、审批时限计算时这个函数是主力。如果公司的周末不是周六日或者工作日只有周一到周四就用NETWORKDAYS.INTL它的第二参数能指定周末类型。比如第二参数填“11”代表仅周日休息。这类函数写起来不复杂难在维护节假日清单建议把节假日单独开一个工作表维护不要硬写在公式里。6.3 数据规范比函数更重要的日期表设计习惯最后想聊聊比函数本身更值钱的一个习惯。很多Excel日期问题追根溯源不是函数不会写而是原始数据从一开始就没管好。日期中间夹着空格、月份是中文还是数字、年用两位还是四位这些负面影响会一路传染到透视表、图表和最终结论。我现在的个人规范是日期列必须存真日期不存文本不许左边对齐的日期出现在正式表里输入统一用2025-01-15或2025/1/15不接受“2025.1.15”需要年、月、星期时用辅助列生成不覆盖原始日期列报表最终的“数据截止时间”如果没有必要动态刷新一律静态化这几个规矩看着简单真坚持下来能避开大部分日期坑。前段时间帮朋友清理一张15000行的订单表发现里面光日期格式就有七种清洗完再做月度汇总原来各种对不上的数一下子就顺了。日期函数学得再多不如一开始就把日期存规范。我自己的体会是TODAY、NOW这类函数没什么高深的东西真正考验人的是使用场景和边界意识。什么时候该动态更新什么时候该固化成静态值比函数本身背得熟不熟更重要。你可以在任何一个表里快速写下TODAY()但只有知道它会随系统时钟跳动、会让工作簿变卡、会让“时间戳”变“时间标”的人才算真正用懂了它。下次再做报表不妨先把“日期到底是不是真日期”这一关过了再决定要不要上NOW。这样至少能省下好几个因为日期诡异变化而加班的夜晚。