夜雨聆风
Excel函数——日期与时间函数
Excel函数是数据处理的核心引擎，掌握它能让工作效率实现质的飞跃。本文将由里及表、由浅入深，从日期与时间的存储机制到常用函数，再到综合案例，带你深入了解Excel日期与时间函数。
01 Excel中日期和时间的存储机制
序列值
在Excel中，所有的日期和时间都被存储为数字（称为“序列值”或“序列数”），其整数部分代表自指定起点已过的天数，小数部分代表一天中的时间比例。例如，“2000-04-06 12:07:00”在Excel中将被存储为36622.50486111…，这是因为2000年4月6日自1900年1月1日以来已过36622天（Excel默认采用1900日期系统），12:07:00是(12*60*60+7*60)/(24*60*60)=0.50486111…天。
序列值的表现形式
=TODAY() '返回当前日期的序列值(动态更新)=NOW() '返回当前日期和时间的序列值(动态更新)
=YEAR(序列值serial_number) '返回对应于某个日期的年份(1900-9999之间的整数)=MONTH(序列值serial_number) '返回日期中的月份(1-12之间的整数)=DAY(序列值serial_number) '返回日期中的天数(1-31之间的整数)=HOUR(序列值serial_number) '返回时间值的小时数(0-23之间的整数)=MINUTE(序列值serial_number) '返回时间值中的分钟(0-59之间的整数)=SECOND(序列值serial_number) '返回时间值的秒数(0-59之间的整数)=DATE(年year,月month,日day) '返回表示特定日期的序列值=TIME(时hour,分minute,秒second) '返回表示特定时间的十进制数字(0到0.99988426之间的小数)
=EDATE(开始日期start_date,月份数months) '返回表示某个日期的序列值，该日期与指定日期相隔(之前或之后)指示的月份数=EOMONTH(开始日期start_date,月份数months) '返回某个月份最后一天的序列值=WORKDAY(开始日期start_date,工作日天数days,[假日holidays]) '返回在起始日期之前或之后、与该日期相隔指定工作日(不包括周末和专门指定的假日)的某一日期的序列值
格式转换
=DATEVALUE(文本型日期date_text) '将存储为文本的日期转换为序列值=TIMEVALUE(文本型时间time_text) '返回由文本字符串表示的时间的十进制数字(0到0.99988426之间的小数)
在实际工作中，经常会遇到从外部系统导入的日期时间数据以文本形式存储，如“2000-04-06”或“12:07:00”。这类数据虽然看起来像日期或时间，但在Excel中实际被识别为普通字符串，无法直接参与日期时间计算、排序、筛选，也无法与日期时间函数配合使用。此时，就需要借助DATEVALUE函数和TIMEVALUE函数，将文本型日期和时间转换为Excel内部可识别的日期序列值和时间小数部分，从而为后续的数据分析、图表制作和复杂计算奠定基础。
需要注意的是，作为参数的文本型日期和时间需要符合Excel的识别规则，若文本格式完全不符，如“2000.04.06”或“十二点零七分”，DATEVALUE函数和TIMEVALUE函数会返回错误“#VALUE!”，此时需视情形对参数进行预处理，如使用SUBSTITUTE函数将“2000.04.06”中的点“.”替换为短横杠“-”。
区间分析
=DAYS(结束日期end_date,开始日期start_date) '返回两个日期之间的天数=DAYS360(开始日期start_date,结束日期end_date,[计算模式method]) '返回两个日期之间的天数(每个月以30天计，一年共计12个月)=NETWORKDAYS(开始日期start_date,结束日期end_date,[假日holidays]) '返回两个日期之间的工作日(不包括周末和专门指定的假日)天数=NETWORKDAYS.INTL(开始日期start_date,结束日期end_date,[周末日weekend],[假日holidays]) '返回两个日期之间的工作日(不包括专门指定周末日和假日)天数
DAYS函数用于直接计算两个日期之间相差的实际天数，其不区分周末和节假日，完全按照日历上的实际天数进行减法运算；DAYS函数则采用财务和会计领域常用的一年360天（每月30天）的规则来计算两个日期之间相差的天数，其会将不同月份的实际长度统一折算，使得每个月的天数标准化，从而方便利息、租金、贴现期等金融指标的计算。
NETWORKDAYS函数用于计算两个日期之间的工作日天数，其默认以周六和周日为周末，自动排除这些非工作日，并且允许用户额外指定一个包含法定节假日的区域，将这些日期也从工作日中剔除。该函数在计算项目实际工时、人员考勤、任务排期、供应链交付周期等场景中非常实用，因为其只统计员工或系统正常工作的天数，能够更准确地反映真实的工作耗时。
NETWORKDAYS.INTL函数是NETWORKDAYS函数的增强版，其与后者最大的区别在于可以自定义周末的设置，用户可以通过数字代码或字符串来指定哪几天算作周末，例如某些国家或地区以周五和周六为周末，或者企业实行周日单休、大小周等特殊排班制度，都可以通过这个函数灵活适配。因此，NETWORKDAYS.INTL函数适用于企业个性化排班管理，以及任何需要根据非标准周末来精确计算工作日的场景。
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
周期定位
=WEEKDAY(序列值serial_number,[返回值类型return_type]) '返回对应于某个日期的一周中的第几天。=WEEKNUM(序列值serial_number,[返回值类型return_type]) '返回指定日期在该年中所处的周数
WEEKDAY函数和WEEKNUM函数是用于日期星期和周次分析的核心函数——前者返回一个日期在星期中的第几天，另一个返回一个日期在一年中所处的周数。通过合理使用这两个函数，可以轻松实现按星期或按周的数据聚合、条件判断和可视化分析。
需要注意的是，WEEKDAY函数和WEEKNUM函数的第二个参数虽然都是可选参数，但“省略”和“1”均意味着函数在执行时，默认一周的第一天为星期日（对应数字1），最后一天为星期六（对应数字7），而参数“2”才意味着函数在执行时默认一周的第一天为星期一（对应数字1），最后一天为星期日（对应数字7）。这一差异在判断周末、按周统计时尤为关键。因此，为符合国内工作习惯，在使用这两个函数时应明确指定第二个参数为“2”。
04 日期与时间函数的应用案例
应用场景：某食品企业主营多种保质期产品，库存管理面临严格的时效性要求。每批产品入库时均记录生产日期和保质期月数，系统需要据此自动计算出每个产品的保质期截止日（DATEVALUE/EDATE函数），并实时跟踪当前日期与该截止日之间的剩余自然天数（DAYS/TODAY函数），以便及时发现即将过期或已过期的产品。同时，由于实际运营中涉及生产排期、配送调度和促销安排，企业还需要了解在剩余时间内有多少个工作日（NETWORKDAYS函数），从而更精准地制定处置策略。采购部门则希望根据产品的生产周期和节假日安排，自动推算出最迟的采购下单日期（WORKDAY函数），确保在下一个生产周期开始前有足够的库存。其中，下一生产日期设定为下一月的月末（EOMONTH函数），建议补货日则为该生产日期前7个工作日，以保证备货时间充足并合理避开节假日影响。
