编按: 非标准格式的时间不能直接加减获得时长,必须转换成标准格式后才行。但是怎么才能把非标准时间如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/14 0: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<a2,1,0) 完美解决! 关于非标准格式的飞机飞行时长计算就说到这。 |
|