Excel非标准格式时间计算方法：可先用text函数进行转换

编按：非标准格式的时间不能直接加减获得时长，必须转换成标准格式后才行。但是怎么才能把非标准时间如1230转成标准时间12:30呢？就看你怎么用TEXT函数了。

今天和大家讨论非标准格式时间计算，如飞机飞行时间计算，如下图所示。

数据中用四位数字表示时间，例如1245的意思是12:45。

只要将数据转换为标准格式时间，则可用到达时间-起飞时间计算飞行时间。

想象中用公式=TEXT(B2,"00:00")-TEXT(A2,"00:00")就可以，实际却是这样的：

为什么会这样？逐个检查发现“=TEXT(B2,"00:00")”就已发生错误。

说明B2的数据无法按照"00:00"格式显示。

实质上“00:00”并非Excel可识别的格式代码。试试，输入数据如1245，然后CTRL+1自定义格式，格式代码为“00:00”，确定时提示不可用。

更换为时间格式代码，如“hh:mm”,公式“=TEXT(B2,"HH:MM")”得到的是“00:00”，也不是需要的“12:30”。

任何非时间的正数在时间格式面前都是“年月日 h:mm:ss”形式的数字。整数部分对应年月日，小数部分对应时分秒。B2中1230在时间格式面前就是“1903/5/140:00:00”，TEXT函数截取小时和分钟得到“00:00”。

看来此处只能用强了，在冒号前加感叹号，强制显示冒号。公式=TEXT(B2,"00!:00")。

结果正常！再试试=TEXT(B2,"00!:00")-TEXT(A2,"00!:00")：

大部分正确，个别出现一串#，又是什么情况？

现在单元格都是时间格式，改成常规：

#号的地方都是负数，这是航班时间超过凌晨12点（跨天）导致的。

为了避免这种错误，到达时间小于起飞时间的，就要加1（表示1天）。

公式=TEXT(B2,"00!:00")-TEXT(A2,"00!:00")+IF(B2

完美解决！

关于非标准格式的飞机飞行时长计算就说到这。

做Excel高手，快速提升工作效率，部落窝教育Excel精品好课任你选择！

学习交流请加微信hclhclsc进群领取资料

Excel是怎么自动识别日期和时间的？

TEXT函数常用套路

自定义格式中的代码含义和用法

自定义格式让数据以万为单位显示并保留不同小数

版权申明：

本文作者老菜鸟；部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。