【置顶公众号】或【设为星标】及时接收不迷路
案例数据如下图，其中，A列中的数据就是关于时长的数据记录。从数据的格式方面来看，并不是所有数据都按照“XX天XX小时XX分钟XX秒”这种比较规范的格式来录入的，数据“混搭”比较严重。
今天，让我们一起来学习一下此类问题的处理方法。
首先，我们先来分析一下时间的换算关系。
以A2中的数据为例，11天21小时16分钟52秒如果想换算成小时的话，应该是这样的一个换算方式：11*24+21*1+16/60+52/3600。
为了便于大家理解，阿硕对本例中每一个时长的运算逻辑做了整理，如下图所示。
好了，讲完了时间换算的逻辑，就可以得出解决今天这个问题的思路了：先将原始数据转换成运算表达式（使用SUBSTITUTE函数），然后再对这个运算表达式进行求和（使用EVALUATE函数）。
下面，就让我们一起来操作吧！
1.使用SUBSTITUTE函数替换“天”
我们在B2中输入“=SUBSTITUTE(A2,"天","*24+")”，然后向下复制填充公式，得到的结果如下图所示。可以看到，A2中的“11天”变成了“11*24+”，这就为天数的转换做好了准备。小伙伴们在这里一定要注意一点，那就是在24后面还要有一个加号（“+”）。因为如果不写这个加号的话，我们构造的表达式在后续的运算中就会出问题啦！
2.使用SUBSTITUTE函数替换“小时”
将天数替换完成之后，我们再来替换小时。这时，我们使用函数嵌套来进行操作。我们使用上一步骤中的SUBSTITUTE函数，作为本步骤中SUBSTITUTE函数的第一参数。
我们将B2中的公式修改为“=SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小时","+")”，然后向下复制填充公式，得到的结果如下图所示。可以看到，A2中的“21小时”变成了“21+”，这就为小时的转换做好了准备。（注：将“天”替换成“*1+”也是可以的哦~）
3.使用SUBSTITUTE函数替换“分钟”
将小时替换完成之后，我们再来替换分钟。我们使用SUBSTITUTE函数嵌套来进行操作。我们将B2中的公式修改为“=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小时","+"),"分钟","/60+")”，然后向下复制填充公式，得到结果的如下图所示。可以看到，A2中的“16分钟”变成了“16/60+”，这就为分钟的转换做好了准备。
4.使用SUBSTITUTE函数替换“秒”
将分钟替换完成之后，我们再来替换秒，我们继续使用SUBSTITUTE函数嵌套。由于“秒”是我们最后一个要替换的时间单位，所以在直觉上，我们的第一想法是只要将“秒”替换成“/3600”就行了，不需要再额外写一个加号。我们把B2中的公式修改为“=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小时","+"),"分钟","/60+"),"秒","/3600")”，然后向下复制填充公式，得到的结果如下图所示。
此时，我们再观察一下数据，可以发现，B6、B9、B10、B11单元格均以加号作为结尾，对于这样的逻辑表达式，如果直接进行运算的话，是会产生问题的。看来直觉不是太可靠，我们还需要再改进一下函数。
该如何改进呢？首先，我们在“3600”后面加上一个加号试试。我们将B2中的公式修改为“=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小时","+"),"分钟","/60+"),"秒","/3600+")”，得到的结果如下图所示。
由上图可以看到，所有的逻辑表达式的最后一个字符都变成加号了。不过大家别紧张，我们再微调一下函数，就可以轻松搞定！我们知道，任何数据加上0之后，数据还是等于其本身，所以，我们再通过连接运算符（“&”），在数据后面连接上一个0，这样可以既保证运算结果不变，又能够完美地解决了数据以加号作为结尾的问题。我们将B2中公式修改为“=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小时","+"),"分钟","/60+"),"秒","/3600+")&0”，得到的结果如下图所示。
5.使用EVALUATE函数计算时间运算表达式
在前述4步中，我们已经构造了一个关于时间转换的逻辑表达式。想要对这个表达式求和的话，我们要用到一个宏表函数——EVALUATE函数。感兴趣的小伙伴们，可以在咱们公众号内搜索一下它的用法。
EVALUATE是常用的宏表函数，它的作用是对以文本表示的一个公式或表达式求值，并返回结果。它的语法结构比较简单——只需要把要计算的表达式放在EVALUATE后面的括号内，就行了，如下：
=EVALUATE(formula_text)。
其中，formula_text是一个以文本形式表示的表达式。
在使用宏表函数时，有一点要注意：宏表函数只能通过定义名称来使用。下面，让我们来学习具体操作。
在进行定义名称的操作之前，请大家先将B2中的公式等号（“=”）之后内容复制一下（即“SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小时","+"),"分钟","/60+"),"秒","/3600+")&0”）。这是因为，在接下来我们定义名称的时候，在EVALUATE函数前面也会有等号，如果此时把SUBSTITUTE嵌套函数前面的等号也复制过去的话，就会出现两个等号了，还要再额外进行删除操作。
复制完公式之后，我们按Ctrl+F3，则会弹出名称管理器，如下图所示。
点击“新建”，弹出“新建名称”对话框，如下图所示。
接下来，我们来对“名称”和“引用位置”这两处进行修改。我们将“名称”修改为“时间转换”；在“引用位置”后面的输入框中，输入“=evaluate”，然后将刚才我们复制的那一长串不包含等号、层层嵌套的SUBSTITUTE函数粘贴到EVALUATE函数的括号中，如下图所示。（注：由于函数太长，所以截图不全，感兴趣的小伙伴可以拖动鼠标进行查看）
点击“确定”后，再次回到“名称管理器”对话框。可以看到，其中多出了“时间转换”这样一个自定义名称，如下图所示。然后，我们点击“关闭”，将名称管理器关闭即可。
接下来，就是见证奇迹的时刻了！我们在B2中输入“=时间转换”，然后向下复制填充公式，就可以得到我们一直苦苦追寻的以小时为单位的时间啦，如下图所示。
小彩蛋：
在完成时间的转换之后，再为大家补充一个小彩蛋。EVALUATE函数是宏表函数，而宏表函数的在保存的时候，有一点需要注意。
我们现在点击“保存”按钮，则会发现，弹出了一个对话框，如下图所示。
此时，大家一定要选择“否”，并在接下来弹出的界面中，将“保存类型”选择为“Excel 启用宏的工作薄”，然后点击“保存”，如下图所示。（注：如果在此处选择“是”，再打次开Excel时，则会发现，宏表函数无法再次使用了）
点击“保存”后，Excel将包含宏表函数的表为我们进行了另存，另存成的表格是一个启用宏的Excel文件，其数据格式为“.xlsm”。小伙伴们如果感兴趣，可以去看一下这个新生成的Excel的图标，它和我们常见的Excel图标还是略有不同的，如下图左侧所示。
小伙伴们，今天的学习内容就是这些，你学废了吗？返回搜狐，查看更多
责任编辑：}

前言｜职场实例 在日常的职场办公场景中，我们会经常遇到“日期+时间点”复合格式的时间数据，我们需要将两个这种格式的数据进行计算，得到时间差，而且最后要以“X小时X分钟”格式显示时间差。之前在一篇文章中讲解过这样的问题，由于之前的方法受一定情况束缚，只能解决特定的模型，不能完美的解决所有的情况。所以今天小编又总结了一种升级版的方法，可以完美的解决“日期+时间点”复合格式的时间数据计算时间差的问题。公式｜解决方案我们可以在C2单元格直接输入函数公式：=LEFT(TEXT(TEXT(B2-A2,"[M]")/60,"0.00"),2)&"小时"&ROUND(RIGHT(TEXT(TEXT(B2-A2,"[M]")/60,"0.00"),2)/100*60,0)&"分"下拉填充即可得到所有的结果。如下图所示：详解｜长公式拆解理解我们看到上面的公式很长，嵌套了多个函数，比如LEFT函数，RIGHT函数，TEXT函数，ROUND函数等。每个函数都非常的基础，其实整个公式很好理解，下面我们拆解公式理解一下。①获取分钟时间差C2单元格输入公式：=TEXT(B2-A2,"[M]")②将分钟时间差除以60得到小时时间差D2单元格输入公式：=C2/60③将小时时间差批量转换为两位小数格式E2单元格输入公式：=TEXT(D2,"0.00")④提取小时时间差的“小时”数据部分在F2单元格输入公式：=LEFT(E2,2)&"小时"用LEFT函数从左向右进行提取2位。⑤提取转换小时时间差的“分钟”数据部分在G2单元格输入函数：=ROUND(RIGHT(E2,2)/100*60,0)&"分"用RIGHT函数从右向左进行提取2位，然后除以100再乘以60，最后用ROUND函数四舍五入保留整数得到分钟数部分。⑥合并“小时”与“分钟”在H2单元格输入公式：=F2&G2}