用好这 2 个 Excel 公式，和 Ctrl+V 说再见

原文标题：《哪位高人琢磨出这 2 个 Excel 公式，Ctrl+V 要说再见了！》

大家好，我是在研究 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 （ID：excel100），作者：小爽 本文编辑：雅梨子、竺兰