Excel 中有一个隐藏函数 datedif,经常用来计算两个日期之间相差的日子,这个日子可以是各种单位。
有同学提问:Power Query 是否有类似 datedif 的函数?Excel函数(五) – 用 datedif 写情书
那么就来看看今天的案例。
案例:
计算下图 1 中的每个日期与 2025 年 12 月 31 日相差的年数、月数和天数。
效果如下图 2 所示。
解决方案:
如果用 datedif 函数,这个案例很容易。
1. 年:选中 C2:C31 区域 --> 输入以下公式 --> 按 Ctrl+Enter:
=DATEDIF(B2,"2025/12/31","y")
datedif 函数释义在这里就不展开了,具体详解大家可以参阅之前的推文。
2. 月:选中 D2:D31 区域 --> 输入以下公式 --> 按 Ctrl+Enter:
=DATEDIF(B2,"2025/12/31","m")
3. 日:选中 E2:E31 区域 --> 输入以下公式 --> 按 Ctrl+Enter:
=DATEDIF(B2,"2025/12/31","d")
接下来回到今天的案例:在 PQ 中有没有上述公式?
1. 选中数据表的任意单元格 --> 选择工具栏的“数据”-->“从表格”
2. 在弹出的对话框中保留默认设置 --> 点击“确定”
表格已上传至 Power Query。
3. 点击“日期”左侧的格式按钮 --> 在弹出的的菜单中选择“日期”
4. 在弹出的对话框中点击“替换当前转换”。
5. 选择工具栏的“添加列”-->“自定义列”
6. 在弹出的对话框中输入以下公式 --> 点击“确定”:
#date(2025,12,31)
#date 的作用是生成一个日期。
7. 再次选择工具栏的“添加列”-->“自定义列”
接下来我们先来计算相隔的天数,因为这个比较简单,有个现成的 M 函数。
8. 在弹出的对话框中将列名设置为“日”--> 输入以下公式 --> 点击“确定”:
Duration.TotalDays([自定义]-[日期])
公式释义:
计算参数中两个日期相减以后,所相差的天数。
接下来计算年和月,这回没有专门的函数了,需要自己设置计算公式。
9. 选择“添加列”-->“自定义列”
10. 在弹出的对话框中将列名设置为“年”--> 输入以下公式 --> 点击“确定”:
Date.Year([自定义])-Date.Year([日期])
公式释义:
这个公式的作用就是用 Date.Year 函数分别将两个日期中的年份提取出来,再相减,得出差值
11. 再一次选择“添加列”-->“自定义列”
12. 在弹出的对话框中将列名设置为“月”--> 输入以下公式 --> 点击“确定”:
(Date.Year([自定义])-Date.Year([日期]))*12+Date.Month([自定义])-Date.Month([日期])
公式释义:
(Date.Year([自定义])-Date.Year([日期]))*12:计算两个日期相差的年份,并乘以 12,转换成月份;
Date.Month([自定义])-Date.Month([日期]):提取出两个日期的月份,并计算差值;
将上述两个数值相加,就是日期之间差异的月份数
13. 删除前三列。
14. 将“日”拖动到最后一列。
15. 选择工具栏的“主页”-->“关闭并上载”-->“关闭并上载至”
16. 在弹出的对话框中选择“表”--> 选择“现有工作表”及所需上载至的位置 --> 点击“加载”
右侧的绿色表格中的结果就跟 datedif 计算出来的完全一样。