Excel 日期库全面指南:核心函数与实战技巧

一、Excel 日期本质

底层逻辑：

Excel 以 序列号（Serial Number） 存储日期

1 = 1900 年 1 月 1 日

45000 = 2023 年 3 月 15 日

时间存储为小数：

0.5 = 中午 12:00

=NOW() → 返回 45000.70833（2023 年 3 月 15 日 17:00）

二、核心日期函数库

类别 函数 公式示例 用途

基础获取 TODAY() =TODAY() 返回当前日期（动态更新）

NOW() =NOW() 返回当前日期+时间

日期构造 DATE(year,month,day) =DATE(2023,12,31) 组合年月日生成有效日期

提取组件 YEAR(date) =YEAR(A2) 提取年份

MONTH(date) =MONTH(A2) 提取月份（1-12）

DAY(date) =DAY(A2) 提取日（1-31）

WEEKDAY(date, [type]) =WEEKDAY(A2,2) 返回星期几（2 表示周一=1）

日期计算 EDATE(start_date, months) =EDATE("2023-01-15",3) 计算 N 个月后的日期

DATEADD(start_date, days) =A2+30 日期加减（直接运算）

DATEDIF(start_date,end_date,unit) =DATEDIF(A2,B2,"M") 日期差计算（隐藏函数）

工作日计算 WORKDAY(start_date, days, [holidays]) =WORKDAY(A2,10,B2:B5) 排除周末/假期的到期日

NETWORKDAYS(start_date,end_date,[holidays]) =NETWORKDAYS(A2,B2,B2:B5) 两日期间工作日天数

关键技巧：

用 TEXT 函数格式化日期：=TEXT(A2,"yyyy-mm-dd dddd") → 2023-03-15 星期三

计算年龄：=DATEDIF(B2,TODAY(),"Y")&"岁"

三、实战场景解决方案

场景 1：动态日期范围

=EDATE(TODAY(), -1) // 上月今天

=TODAY()-WEEKDAY(TODAY(),2)+1 // 本周周一

=EOMONTH(TODAY(),0) // 本月最后一天

场景 2：账期计算（含节假日）

=WORKDAY.INTL(

A2,

30,

1, // 周末模式（1=仅周日休息）

$C$2:$C$10 // 节假日列表

)

场景 3：生成连续日期序列

在A2输入起始日期 → 选中A2:A100 → 填充序列 → 日期 → 按工作日填充

四、日期处理避坑指南

格式陷阱

输入日期后显示数字 → 右键单元格 → 设置格式 → 日期

用 DATEVALUE 转换文本日期：=DATEVALUE("2023/5/1")

跨年计算错误

// 错误：=YEAR(B2)-YEAR(A2)

// 正确：=DATEDIF(A2,B2,"Y")

时区问题

UTC 时间转本地：=A2 + TIME(8,0,0)（+8 小时）

五、高阶技巧：动态日期表

=LET(

startDate, DATE(2023,1,1),

endDate, DATE(2023,12,31),

dates, SEQUENCE(endDate-startDate+1,1,startDate),

CHOOSE({1,2,3,4},

dates,

TEXT(dates,"yyyy-mm"),

WEEKDAY(dates,2),

IF(WEEKDAY(dates,2)>5,"周末","工作日")

)

)

生成带月份/星期/工作日标记的全年日历

六、最佳实践

数据验证

设置日期范围限制：数据 → 数据验证 → 日期（2020-1-1 至 2050-12-31）

快捷键

插入当前日期：Ctrl + ;

插入当前时间：Ctrl + Shift + ;

模板推荐

项目管理：文件 → 新建 → 搜索“甘特图”

工作日历：模板库 → 日历

重要提示：

1900 年闰年 Bug：Excel 认为 1900 年是闰年（实际不是），但此错误为兼容性保留

跨平台问题：Mac 版 Excel 使用 1904 日期系统（系统偏好 → 计算 → 使用 1904 日期系统）

通过掌握以上核心函数与场景应用，可解决 95%的日期处理需求。建议结合具体业务数据实操练习，2 小时内即可高效入门！