📌 阶段三 · 函数核心 · 第14讲 日期函数 · 约 45 分钟

给应收账款装上「账龄计算器」：自动算出每笔欠款挂账多少天，30/60/90 天分档催款

学习目标

  • 理解 Excel 用序列号存日期：日期是整数、时刻是小数，日期可以直接加减
  • 会用 YEAR/MONTH/DAY/DATE 拆装日期，把流水按月归属
  • 会用 TODAY 配合减法或 DATEDIF 计算账龄天数
  • 完成应收账款表的账龄分档与催款状态判断
  • 会用 TEXT 和自定义格式把日期「整」成想要的显示样子

知识点讲解

Excel 心里的日历：日期是整数，时间是小数

Excel 的日期采用 1900 纪年方式：每个日期其实是一个整数——从 1900 年 1 月 1 日开始数的第几天，整数 1 就代表一天。时刻则是小数，表示这一天已经过去了多少：整数部分是天，除以 24 得小时，再除以 60 得分钟。

这带来一个极实用的性质：日期可以直接加减。出库日期加 30 就是账期到期日；两个日期相减就是间隔天数。唯一要注意的是显示：相减的结果 Excel 会自作聪明显示成一个日期，把结果单元格的数字格式改成「数值」就恢复天数。

=出库单!B2+30

出库日期加 30 天，直接得到「月结 30 天」的应收到期日。

=TODAY()-D2

今天减最后出库日期，得到挂账天数——前提是 D 列是真正的日期格式，结果单元格设为数值格式。

💡 提示:

  • 日期加减后显示成奇怪日期？把单元格数字格式改成「数值」即可
  • 想知道某天到某天差几小时，用两日期相减再乘 24

YEAR/MONTH/DAY/DATE：日期的拆装四件套

YEAR、MONTH、DAY 分别从一个日期里取出年、月、日三个数字；DATE 反过来，把年、月、日组装成一个标准日期：=DATE(年,月,日)。

DATE 还很聪明：月、日数字过大自动进位，小于 1 自动退位——=DATE(2024,13,1) 得到 2025 年 1 月 1 日，=DATE(2024,1,0) 得到 2023 年 12 月 31 日。

进销存两大用法：一是把出库流水按月归属，=MONTH(出库单!B2) 每行打上月份标签，为月度汇总打地基；二是一式算出本月 1 号：=DATE(YEAR(TODAY()),MONTH(TODAY()),1)。

=MONTH(出库单!B2)

取每笔出库日期的月份号，1~12——按月统计的第一步。

=DATE(2024,13,1)

月份写 13 自动进位，得到 2025-1-1；进位退位特性常用来推月初月末。

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

今年的今天，日设为 1——一式算出本月 1 号。

💡 提示:

  • DATE 的进位特性是合法玩法：算「下个月同一 Day」不必自己判大小月

TODAY/NOW：让表格自己知道今天几号

=TODAY() 返回今天的日期，=NOW() 返回含时刻的当前时间。它们不需要参数，但括号不能省；每次打开或重算工作簿都会自动更新。

账龄的本质就是「今天 − 最后一次出库日期」，所以应收账款表的账龄列只需要一条 =TODAY()-D2。想只算整天数，也可以用下一节的 DATEDIF。

=TODAY()

今天，日期格式显示；参与减法时先把结果格设为数值格式。

💡 提示:

  • TODAY 是「活」的：明天打开文件它会变成明天；要把某天的快照钉死，复制后选择性粘贴为数值
  • NOW 比 TODAY 多带时刻，做打印时间戳合适，算天数用 TODAY 更干净

DATEDIF：算两个日期差了多少年月日

=DATEDIF(开始日期,结束日期,单位) 专门比较两个日期的差。第 3 参数是带英文引号的字母，决定返回什么："y" 整年数、"m" 整月数、"d" 天数；还有 "ym"、"md"、"yd" 三个余数式用法——刨除整年剩几个月、刨除整月剩几天、刨除年剩多少天，相当于做减法后求余。

进销存主战场就是账龄：=DATEDIF(D2,TODAY(),"d") 挂账天数、=DATEDIF(D2,TODAY(),"m") 挂账整月数，给「月结 60 天」的客户判断是否到期很方便。

=DATEDIF(D2,TODAY(),"d")

D2 到今天隔了多少整天——与直接相减等价，但语义更明确。

=DATEDIF(D2,TODAY(),"m")

挂账满几个整月：判断「月结」客户是否到期时比天数更直观。

💡 提示:

  • 第 3 参数的字母必须加英文双引号
  • 开始日期必须在结束日期之前，写反了会报错

WEEKNUM/WEEKDAY：第几周、周几

=WEEKNUM(日期,[return_type]) 返回一年中的第几周；return_type 写 1 表示一周从星期日开始、写 2 表示从星期一开始。

=WEEKDAY(日期,[return_type]) 返回星期几对应的数字（讲义课件把函数名误印成了 WEEKNUM，正确写法是 WEEKDAY）；用 return_type=2 时周一到周日对应 1~7，最直观。

进销存用途：分析出库单周末销量是否更高——=WEEKDAY(B2,2) 得 6、7 就是周六日，配合 COUNTIF 一数便知。

=WEEKNUM(出库单!B2,2)

这笔出库发生在一年中的第几周（周一制）。

=WEEKDAY(出库单!B2,2)

周一=1 到周日=7；结果是 6 或 7 说明是周末单。

💡 提示:

  • WEEKDAY 的 return_type 不同，1~7 的含义不同，统一用 2 免得绕晕

TEXT 与自定义格式：给日期做「整容」

不改数据、只改显示是第 2 讲的自定义数字格式：格式代码 aaaa 显示「星期几」、aaa 只显示「几」；0000-00-00 能把 20240101 这种「假日期」（文本串）显示成 2024-01-01 的模样。

=TEXT(value,format_text) 则按格式代码把数值转成文本，相当于「整容后还复制一份出来」：=TEXT(A2,"aaaa") 得「星期三」，=TEXT(A2,"yyyy年m月") 得「2024年12月」。

月度汇总时 =TEXT(出库单!B2,"yyyy-mm") 生成的年月标签整齐划一，做报表分组特别好用。

=TEXT(出库单!B2,"yyyy-mm")

把日期转成「2024-12」样式的文本标签，同月同标签，直接 COUNTIF 或 SUMIF 分组。

=TEXT(A2,"aaaa")

显示星期几的中文全称。

💡 提示:

  • TEXT 的结果是文本，不能直接再当日期参与加减；要参与运算请用 MONTH/DATE 这类函数
  • 假日期（20240101 文本）能用 0000-00-00 格式「装」成日期，但它仍是文本，函数不会认——要真日期还是得用 DATE 或分列

实战演练

生意做大了难免赊账，这一讲新建「应收账款」工作表：每个客户一行，用 SUMIF 汇总全年买了多少钱、已收回多少钱，算出还欠多少；再按最后出库日期算账龄，30/60/90 天自动分档，超过 90 天直接亮「催款」。这就是月底交给老板的那张「谁欠钱、欠多久」的表，也是期末系统应收账款模块的原型。可先在右侧练习数据里试手感，再回到进销存工作簿正式建造。

  1. 建表：应收账款骨架 — 新建工作表「应收账款」，A1:H1 依次输入表头：客户编号、客户名称、累计销售额、最后出库日期、已收款、应收余额、账龄天数、状态。A2 起录入 12 个客户编号（KH001~KH012）。
  2. 客户名称自动带出 — B2 输入 =VLOOKUP(A2,客户档案!A:B,2,FALSE)，下拉 12 行——第 11 讲的功夫直接复用。
  3. 累计销售额 — C2 输入 =SUMIF(出库单!F:F,A2,出库单!I:I)，下拉：在出库单 F 列（客户编号）里找 A2，把对应的金额列 I 全部加总。
  4. 录入最后出库日期与已收款 — D 列录入每个客户的最后出库日期（练习数据中已给出参考，注意确认真日期格式），E 列录入已收款金额（按销售额的 80%~95% 估算，故意让几个客户留下大额欠款，制造不同账龄）。
  5. 应收余额 — F2 输入 =C2-E2，下拉。销售额减已收款就是还欠的钱；出现负数说明收超了，回头检查 E 列。
  6. 账龄天数 — G2 输入 =TODAY()-D2，下拉。如果结果显示成日期，选中 G 列把数字格式改成「数值」；也可以写 =DATEDIF(D2,TODAY(),"d") 只算整天。两种写法都试一遍，对比结果。
  7. 状态分档：30/60/90 三道线 — H2 输入 =IF(G2>90,"催款",IF(G2>60,"重点跟踪",IF(G2>30,"关注","正常")))，下拉。三道账龄线把客户分成四档，谁该催款一目了然——下一讲还会用条件格式给「催款」行自动标红。

在线练习

下面的表格可以直接编辑,照着演练步骤在本页练习;需要菜单操作的步骤请下载配套练习文件,在自己电脑的 Excel 中完成。

表格加载中…

验收清单

逐项自查,全部通过即完成本讲:

  • ☐ C 列抽查通过：任选一个客户，出库单筛选加总与其销售额一致
  • ☐ G 列显示的是天数数值而不是日期（格式已改数值）
  • ☐ F 列 = C 列 − E 列，全表无负数
  • ☐ H 列四个档位都出现过（可调整已收款制造不同账龄来验证）
  • ☐ 能解释账龄天数为什么是「两个日期序列号相减」，以及结果变日期时怎么处理