用好这2个excel公式，和ctrl v说再见-ag人生就是博

原标题:《这两个excel公式谁算出来的。ctrl v想说再见！》

大家好，我是学excel数据的今天我们就来看看日期时间数据格式的相关知识

当用户浏览网站界面时，系统会自动记录这些时间。

对网站用户停留时间的分析，有助于我们判断用户对网站的粘性程度，以及网站的内容质量是否仍需优化。

首先，我们将这些日期和时间数据从系统导出到excel现在，我们想知道他们之间的操作时间

日期和时间的本质是数值，可以直接加减。

但如下图所示，以上从系统导出的日期时间格式并不规范，我们需要提前将其转换成标准的日期时间格式。

下面，我们来看看两种做法:

① excel功能。

② powerquery .

pspowerquery是office excel 2016及以上版本自带的插件，2013版本需要单独安装该插件

01.excel函数

在excel中，当日期格式转换为标准日期时，一般我们想到的第一个函数就是——text函数，它可以使单元格数值按照指定的格式显示。

在案例中，20220613 17:10:19，它包含两部分，日期 时间。

用于日期提取的▋:

使用left函数提取以前的日期数字。

=左

将日期数字字符串格式转换为日期格式文本字符串，这里我们使用text函数。

=文本

=text，"0000—00—00 ")

要将文本日期字符串转换为数字，我们只需在公式前加—即可。

然后将数字格式修改为短日期，可以显示为日期。数字格式转换为日期格式后:

▋提取的时间:

如下所示，我们使用右函数提取时间。

=对

我们知道日期的本质是数值，而时间的本质是小数，所以日期时间就是日期 时间。

此时，我们已经通过公式将非标准日期和时间更改为标准日期和时间格式。

再次组合公式。

= — text，" 0000—00—00") right(a2，8)

回到案例，公式用于将提交时间和操作时间转换为标准格式。

减去两者的日期和时间既然我们需要的是时长，也就是小时数，又因为1天= 24小时，所以还需要* 24

小补充:

使用数组公式组合三个公式在这里看不懂也没关系我们也可以直接把公式写长一点，把两个日期时间相减，再相乘

ps。除了office365，数组公式需要按三个键才能完成，输入公式后按enter不行~

=sum，"0000—00—00") right(a2:b2，8))*—1，1)*24

至此，excel函数介绍完毕~

接下来，我们进入pq法的解释。

02.pq实践

说到pq，第一步，你知道我们要做什么吗。

不管怎样，首先将我们的数据源导入pq。

检查数据源—选项卡—(来自表格/区域)—(确定)。

ps不同版本的excel导入操作路径可能略有不同

在pq中，虽然日期时间不能直接识别20220823 18:00:00这样的格式从函数来看，它可以识别t左边的日期和右边的时间

所以我们可以通过用t替换空值来转换它。

我们来看看界面操作。

替换该值。

选择两列—在选项卡下—单击(替换值)—要找到的值是:null—替换为:t。

确定日期。

选择两列，在选项卡下，单击日期下的分析按钮，然后简单地修改公式。

因为界面上的分析功能只有日期/时间，没有日期/时间类型的分析，所以我们还需要修改公式。

变更前:

1，=由=表替换的值transformcolumns，2，提交时间，每个日期从)，键入日期，3，操作时间，每个日期

变更后:

=值替换为=表transformcolumns，提交时间，每个日期时间从， "操作时间 "，每个日期时间

左右滑动以查看

动画的操作效果:

添加一列以计算两列之间的差异。

在选项卡—(自定义列)—输入公式—(确定)。

=数字。从—(提交时间))*24

至此，效果已经完成，可以导出数据进行下一步分析了。

如果你还在疑惑怎么导出pq数据，那就不要~

pq的界面做法，这里，介绍完毕~

接下来我要说的是pq的另一种方法，稍微难一点目的是拓展大家的思路

03.延伸和扩展

想法1:用pq可以识别的文本日期和时间替换字符串，并使用datetime从函数返回它

公式如下:

1，=表addcolumn))，3，时差，4，每个数字

思路二:通过datetime逐一识别对应的格式信息。来自文本

小贴士:

格式: " yymmdd hh:mm:ss "

和minute都是m，所以为了区分，大写的m代表月份，小写的m代表分钟，h (24小时)，h (12小时)。

这里的时间是24小时的形式，所以这里的小时是h。

公式如下:

1，=表addcolumn))，5，时差，6，每个数字

只剩1%的电量了。

04.临终遗言

本文描述了如何计算两个日期之间的时间。」

如果日期时间是标准的，那就很简单，直接减法。

但是在实际操作中，我们大多数人面对的都是不规则的日期和时间，所以需要掌握换算的方法。

excel中经常使用text函数，将数值文本格式化为标准的日期格式，然后进行数值转换。

在pq中，分析函数可以识别出大部分不规则的日期，但有少数仍然需要我们进行巧妙的转换。

。