在Power Pivot中用DATEDIFF函数计算两个日期之间的间隔

赵文超

经常有朋友问在Power Pivot中是否有类似Excel中的DATEDIF函数，用来计算2个日期之间的间隔。如果你用的是Excel 2016 或Office 365，或Power BI Desktop，那么DAX中提供了新的DATEDIFF函数可以实现。如果你用的是Excel 2013，还没有这个DAX函数，就需要用其它组合函数来实现。

我们先来看一下Excel中DATEDIF的用法：

通过函数=DATEDIF(A2,B2,"Y")得到两个日期之间的年的间隔。如果把第三个参数"Y"换成"M"或"D",则分别对应月和日。

将数据导入到Power Pivot中，输入DATEDIFF（ 注意，是DATEDIFF, Excel中是DATEDIF，差了一个 F ），DATEDIFF 需要3个参数，日期1，日期2，和间隔单位。

=DATEDIFF('测试'[开始日期],'测试'[结束日期],YEAR)

最终计算结果如下：

同理，如果我们把DATEDIFF的第三个参数改成Month和 Day，则可以获取2个日期之间间隔的月份数和天数。还可以得到季度、小时、分钟、秒数等。

使用DATEDIFF可以方便地计算日期时间间隔，但有一点需要注意，大家看下图中红圈的内容。Excel里的DATEDIF和DAX里的DATEDIFF计算结果不一致：

当开始日期是6/30/2016，结束日期是6/29/2017时，Excel中DATEDIF(A2,B2,"Y") 公式的计算结果严格按照日期计算，没有到一年就算0，而DAX中DATEDIFF('测试'[开始日期],'测试'[结束日期],YEAR) 的计算则是按照两个日期所处的年份来计算，2017-2016=1年。

所以如果你在Power Pivot中不想要这样的计算方法，比如计算年龄的时候，想实现Excel中DATEDIF的计算方法，则可以使用下面的公式：

=YEARFRAC('测试'[开始日期],'测试'[结束日期])

然后通过ROUNDDOWN向下取整实现Excel中DATEDIF整年的计算：

=ROUNDDOWN(YEARFRAC('测试'[开始日期],'测试'[结束日期]),0)

同理，如果我们用向上取整函数ROUNDUP则可以实现和DAX中DATEDIFF一样的计算结果。而这个函数组合也可以在Excel2013中使用，也就是没有DATEDIFF的情况下实现间隔年的计算。

【间隔月份的计算】

间隔月份也是工作中比较常见的计算，下面我们同样拿Excel中的公式：

=DATEDIF(A2,B2,"M")

和Power Pivot中的公式

=DATEDIFF('测试'[开始日期],'测试'[结束日期],MONTH)

来进行对比

我们看到两个计算结果依然存在差异，Excel中的DATEDIF当日期没有满一个月的时候不计算，而Power Pivot中计算（ 参考上图第一行数据 ）。

我们尝试用其它函数组合实现该计算（Excel2013的Power Pivot也可用）：

=(YEAR('测试'[结束日期])-YEAR('测试'[开始日期]))*12+MONTH('测试'[结束日期])-MONTH('测试'[开始日期])

该计算的意思是先用两个日期间隔的年份数乘以12个月，然后加上[结束日期]中的月份数减去[开始日期]中的月份数，结果和DATEDIFF-DAX一样，忽略日期，直接用月份相减。

我们还是关注第一行数据，如果开始日期是6/30/2016，结束日期是6/29/2017，还差1天才满12个月，如果我们希望只计算间隔的整月，也就是计算结果为11，可以对公式进一步调整：

=IF(DAY('测试'[结束日期])>=DAY('测试'[开始日期]),0,-1)+(YEAR('测试'[结束日期])-YEAR('测试'[开始日期]))*12+MONTH('测试'[结束日期])-MONTH('测试'[开始日期])

通过判断2个日期之间日的区别，来决定是否计算：如果结束日期的日大于等于开始日期的日，则直接计算即可，否则在计算结果上-1，实现间隔整月的计算。

这个计算方法，不管是在Excel2013还是2016的Power Pivot中都可以使用。

【间隔日，小时、分钟、秒的计算】

剩下的间隔日、小时、分、秒就比较简单了。可以直接用DATEDIFF来实现

间隔日=DATEDIFF('测试'[开始日期],'测试'[结束日期],DAY)

间隔小时=DATEDIFF('测试'[开始日期],'测试'[结束日期],HOUR)

间隔分钟=DATEDIFF('测试'[开始日期],'测试'[结束日期],MINUTE)

间隔秒=DATEDIFF('测试'[开始日期],'测试'[结束日期],SECOND)

在Excel2013 Power Pivot中也可以直接用日期相减：

间隔日='测试'[结束日期]-'测试'[开始日期]

间隔小时=24*('测试'[结束日期]-'测试'[开始日期])

间隔分钟=24*60*('测试'[结束日期]-'测试'[开始日期])

间隔秒=24*60*60*('测试'[结束日期]-'测试'[开始日期])

这就是我们今天的分享内容，更多Power Pivot DAX函数的用法请参考我们的视频课程- Excel Power Pivot数据建模分析（基础篇）。

*PowerPivot工坊原创文章，欢迎转载，请注明出处