1秒显示总时长——[h]:mm 你会用吗？

徐军泰 Excel表格学院

个人微信号 | （ID：BGXujuntai）

服务号 | Excel表格学院（ID：BGExcel）

微信公众号 | Excel表格星球（ID：Excel-Star）

           

 

很多人在表格中用“8:45”这样的形式来记录所用的时间，如上表所示，代表用时为8小时45分钟。

 

实际上，当这样输入到Excel中时，Excel默认的含义为8点45分。

 

表格学院团队为了记录一项比赛的用时，也用这样的方式记录各位的用时如上，现在想要在第10行计算出大家的总用时，并且也显示为“总的小时:总的分钟”这样格式，怎么办？

 

时间和日期，其实本质上也是数字。

 

所以，我们可以直接用SUM函数对用时求和，得到如下结果：

 

       

      

注：图上公式应为D10单元格

 

但是，问题来了——最后，求和显示的是8:45。总用时为8小时45分钟吗？显示不是，总用时应该为80小时45分钟（大家可以自己加一下）。

 

为什么结果会这样显示呢？因为用SUM求和后，单元格格式与上方单元格格式一致，也是时:分的格式，小时最多不超过24小时。

 

那么如何让单元格显示为真实的总用时和分钟，即80:45呢？下面介绍两种方法。

 

01

自定义格式法

 

完成公式输入后，在公式单元格点击CTRL+1，打开单元格格式设置，在自定义格式中输入代码：[h]:mm，确定。

         

这时，会看到结果变成了我们想要的：80:45。

        

注：图上公式应为D10单元格

 

h代表小时，加上方括号[h]代表显示为超过24小时下的时间的总用时，不加方括号则显示为小于24小时的总用时。

 

 

02

函数法

 

第2种方法，我们可以使用函数，用助于我们理解时间的本质。

       

      

注：图上公式应为D10单元格

 

如果你把整个用时一列，按CTRL+SHIFT+1，就会发现所有时间会变成一个小数。

 

这个小数代表的其实是天的概念，比如0.50就是半天。1天是24小时，所以0.5*24就是12，所以0.5就是12:00。

 

因此，最后求和后的3.36就代表总用时为3.36天，换算过来就是80小时45分钟。

 

那最后，怎么把总用时让它显示为80:45呢？

 

我写了一个函数——

 

       

      

 

通过INT取整等函数的应用，将小数分成整数部分和小数部分。整数部分乘以24，小数部分乘以60，即可转化为总小时和剩余分钟数。