一、如何获取系统当前/当天时间?
获取当前时间,可以使用函数:DateTime.LocalNow()或DateTime.FixedLocalNow()
获取当天日期,需要在当前时间上用Date.From函数来实现:
二、如何计算两个日期的间隔时长、天数?
Power Query里怎么计算两个日期的间隔天数,如果两个日期是标准格式的话,可以直接相减。
在PQ里,日期相减得到的是一个区间(时间段),即这两个日期之间隔了多长(多少天多少小时多少分钟多少秒)。如果希望得到天数的结果,可以直接转换数据格式,比如:
如果希望直接计算天数,你可以直接用函数Duration.TotalDays进行转换,如下图所示:
三、如何把时间自动加上6小时
Power Query里,可以直接用:[时间]+#duration(0,6,0,0)
#duration里的四个参数分别表示:(天,时,分,秒)
四、如何把时间往前/后推1个月?其他时长?
在Power Query里,时间往前/后推1个月,可以使用函数:Date.AddMonths,用法跟Excel里的EDATE完全一样,如下图所示:
而往前(或往后)推多少年,除了转换为多少个月,在Power Query里还直接提供了Date.AddYears函数,如下图所示:
而且,有月、年的,还有AddDays、AddQuarters、AddWeeks,整一个日期全家桶:
上面的函数用法很简单,但是,有一个小小的问题大家可能要注意一下。
我在举例的时候特意用了3月底的一些日期,对应的往前推一个月就是2月底,所以,大家可以观察一下,当月底的日期往前推的时候,是什么情况:
还有,如果往后推一个月呢?月底对应的又是什么?
一定要自己多动手试试哦。
五、’如何计算年龄?
由于PQ里没有类似Excel中的Datedif函数,因此,在PQ中计算常用的间隔天数、年数(年龄),跟在Excel里有所不同——稍微繁琐一点儿,要按照最原始的通过日期计算的方法来求解,但理解了其实也不难。
首先,通过函数Date.ToText可以直接提取月日的格式,比如:
然后,只要判断月日组合的文本大小即可对比日期的月日大小——将日期转换为4位的文本时,文本的排序和再转换为数字的排序是一样的,比如“0513”比“0512”大,跟513比512大是一样的。
此时,通过月日组合比较的结果是true或false,可以直接用Number.From函数转为1或0,所以,最后公式只要用年份差减去转换为数字的判断结果即可:
= Date.Year([当前日期]) - Date.Year([生日])
-Number.From(Date.ToText([生日],"MMdd")>Date.ToText([当前日期],"MMdd"))
如下图所示:
六、如何计算工作日?
经常有朋友问怎么计算两个日期间的工作日问题,本来,对于简单的计数问题,总不会复杂到什么程度,但是,对于这个问题,我通常会说,先确定你的工作日历表,也就是说,先定义好哪些算工作日,哪些算假期——因为每个公司都不一样,甚至每个人都不一样。
当然,无论怎样,我们还是先来看一下完全不需要处理特殊日期的情况。即反正周一到周五就是工作日,周六周日就是休息日,这种情况下,如果用Excel直接解,一个函数搞定:
然鹅,PQ里没有这样的函数,那该怎么办?——一句话,回归自然:选出周一到周五的日期,然后计数!直接上公式:
其中,d = {Number.From([开始日期])..Number.From([结束日期])},即根据起止日期转换为数值后生成相应的区间序列。
下面的List.Select就是上面的d进行筛选,条件为日期的星期几(Date.DayOfWeek,第2个参数为0时,从0开始计算周一),筛选出来后用List.Count进行计数。
在很多问题上,没有现成的函数时,就要考虑用最基础的算法去实现它。
实际工作中,我是从来没见过不需要处理特殊日期的!那么,如果有专门的假期表,该怎么算工作日?
这种情况下,一般来说,应该有一个参与计算的完整的日历表,其中标明了哪些是工作日,哪些是假期——对于很多比较完整的企业数据模型来说,这种完整的日历表应该是比较好的解决方案(如果没有,建议建一个)。样子大概如下:
这种情况下,计算主要就是对日历表进行筛选然后计数:
总结
在实际工作中关于日期及其相关计算的问题,通常都有很多特殊的情况需要处理,比如这个例子中的特殊假期,还有其他的如年假天数计算等等,每个企业都有自己特定的计算方法,大多数情况下都需要回到比较基础的算法来实现,但是,无论怎么变,仍然是这些基础的函数的熟练运用而已。
本文分享自 Excel到PowerBI 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!