大家好，我是在研究 Excel 数据的小爽，今天我们来看看日期时间数据格式的相关知识。
用户在进行网站界面浏览操作的时候，系统会自动记录下这些时间。
对网站用户停留时间的分析，有助于我们判断，用户对于该网站的粘性程度，以及该网站的内容质量是否还需要优化。
我们先将这些日期时间数据从系统导出到 Excel 中，现在，我们想要知道两者之间的操作时长，应该怎么做呢?
日期时间的本质是数值，它们可以直接进行相加减。
但是，如下图，上面这些从系统中导出的日期时间格式不规范，所以我们还需要事先将其转化为标准的日期时间格式。
下面，我们就来看看两种做法：
① Excel 函数 (需要用到 Left 函数，Right 函数，Text 函数)。
② PowerQuery。
PS. PowerQuery (简称 PQ) , 是 Office 的 Excel2016 以及以上版本自带的插件，2013 版本需要另行安装插件
01、Excel 函数
在 Excel 中，将日期格式转化为标准日期，一般我们最先想到的函数是 ——Text 函数，它能够让单元格数值按照指定的格式显示出来。
在案例中，20220613 17:10:19，包含两部分内容，日期 + 时间。
▋对于日期的提取 :
❶ 利用 Left 函数将前面的日期数字提取出来。
=LEFT(A2,8)
❷ 将日期数字串格式转为日期格式的文本串，这里我们用的是 Text 函数。
=TEXT (数字， 格式代码)
=TEXT(LEFT(A2,8),"0000-00-00")
❸ 将文本型的日期字符串改成数字，我们只需在公式前面加上-- 。
然后修改数字格式为短日期，即可显示为日期。数字格式转化为日期格式后 :
▋对于时间的提取 :
如下图，我们利用 Right 函数提取时间。
=RIGHT(A2,8)
我们知道日期的本质是数值，而时间的本质是个小数，所以日期时间就是日期 + 时间。
这时，我们就已经将不规范的日期时间，通过公式改成规范的日期时间格式了。
再合并一下公式。
=--TEXT(LEFT(A2,8),"0000-00-00")+RIGHT(A2,8)
回到案例中，利用公式分别将提交时间和操作时间 ，转为规范的格式。
两者日期时间相减，由于我们需要的是时长，也就是小时数，因为 1 天 = 24 小时，所以还需要 * 24。
小补充 :
利用数组公式合并三个公式，如果这里看不懂也没关系，我们也可以直接公式写长点，两个日期时间相减后再乘。
PS. 除 Office365 之外，数组公式需要按三键【Ctrl+Shift+Enter】结束，输入公式后按回车是不行滴~
=SUM((--TEXT(LEFT(A2:B2,8),"0000-00-00")+RIGHT(A2:B2,8))*{-1,1})*24
到此，Excel 函数做法，就介绍完啦~
接下来，我们进入 PQ 法的讲解。
02、PQ 做法
说到 PQ，第一步，大家知道我们要干啥了吧?
不管三七二十一，先将我们的数据源导入到 PQ 中。
选中数据源 - 【数据】选项卡 -【 来自表格 / 区域】 - 【确定】。
PS. 不同版本的 Excel 导入操作路径可能略有些不同。
在 PQ 中对于 20220823 18:00:00 这类格式，虽然不能利用 DateTime.From 函数直接识别，转化为日期时间格式，但是它可以识别 T 左边日期，右边时间。
所以我们可以通过将空值替换为 T，再来进行转换。
我们来看看界面操作。
❶ 替换值。
选中两列 - 在【转换】选项卡下 - 单击【替换值】 - 要查找的值为: 空 - 替换为：T。
❷ 识别日期。
选中两列，在【转换】选项卡下，单击日期下面的分析按钮，然后简单修改一下公式。
由于界面上的分析功能只有日期 / 时间，没有日期时间类型的分析，所以我们还需要更改公式。
更改前 :
1、= Table.TransformColumns替换的值,
2、"提交时间", each Date.From (DateTimeZone.From (_)), type date},
3、{"操作时间", each Date.From (DateTimeZone.From (_)), type dat)
更改后 :
= Table.TransformColumns替换的值,"提交时间", each DateTime.From(_)}, {"操作时间", each DateTime.From(_))
▲ 左右滑动查看
动图操作效果:
❸ 增加一列计算两列之间差值。
在【添加列】选项卡下 - 【自定义列】 - 输入公式 - 【确定】。
=Number.From ([操作时间]-[提交时间])*24
此时效果已经完成了，后面导出数据进行下一步分析就可以啦。
如果你还在疑惑怎么导出 PQ 数据，那就不应该啦~
PQ 的界面做法，到这里，就介绍完啦~
接下来我要讲的是 PQ 的另一种方法(M 函数)，难度略高，目的是为了拓展一下大家的思路，如果你不感兴趣的话可以跳过这一 Part~
03、延伸拓展
思路一 : 将字符串替换为 PQ 可以识别的文本型日期时间，利用 DateTime.From 函数返回。
公式如下:
1、= Table.AddColumn (
2、Table.TransformColumns (源,{},each DateTime.From (Text.Replace (_,,"T"))),
3、" 相差的时长 ",
4、each Number.From ([操作时间]-[提交时间])*24)
思路二 : 通过 DateTime.FromText 一一识别对应的格式信息
小 Tips:
格式 : "yyyyMMdd HH:mm:ss"
月 (month) 和分 (minute) 缩写都是 m，所以为了区分，大写 M 代表月，小写 m 代表分，H (24 小时)，h (12 小时)。
这里的时间是 24 小时的形式，所以小时这里是 H。
公式如下 :
1、= Table.AddColumn (
2、Table.TransformColumns (
3、源,{},
4、each DateTime.FromText (_,[Format="yyyyMMdd HH:mm:ss"])),
5、"相差的时长",
6、each Number.From ([操作时间]-[提交时间])*24)
电量只剩下 1%。
04、最后的话
本文介绍的是「如何计算两个日期时间之间的时长?」
如果日期时间规范的话，就很简单，直接相减。
但在实际工作中，我们面临的绝大多数是不规范的日期和时间，这时就需要掌握转换的方法了。
Excel 函数中经常用到的是 Text 函数，将值文本格式化为标准的日期格式，然后再来进行数值转换。
而在 PQ 中，分析功能可以识别大多数不规则的日期，但是少数还需要我们进行技巧性转换。
标签： Excel
