在Excel数据处理的复杂世界里，非标准格式数据的计算常常成为令人头疼的难题。今天，我们就聚焦于飞机飞行时间这类非标准格式时间的计算，如下图所示。带大家一步步突破难关。
我们面对的数据是用四位数字来表示时间的，比如 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”，点击确定时，Excel 会明确提示该格式不可用。
更换为时间格式代码，如“hh:mm”,公式“=TEXT(B2,"HH:MM")”得到的是“00:00”，也不是需要的“12:30”。
那我们更换为时间格式代码 “hh:mm” 试试呢？使用公式 “=TEXT (B2,"HH:MM")”，结果得到的却是 “00:00”，和我们期望的 “12:30” 大相径庭。
这背后的原因是什么呢？原来，在 Excel 的时间格式体系里，任何非时间的正数都会被解读为 “年月日 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)
为了彻底解决这个问题，我们需要对公式进行优化。当到达时间小于起飞时间时，要加上 1（表示 1 天）。最终，我们得到了完美的公式 = TEXT (B2,"00!:00") - TEXT (A2,"00!:00") + IF (B2 < A2,1,0) ，成功攻克了非标准格式飞机飞行时长计算的难题。
经过这一番探索，我们不仅解决了飞机飞行时间计算的问题，更重要的是掌握了应对 Excel 非标准格式数据计算的思路和方法。以后再遇到类似难题，大家是不是更有信心了呢？希望今天的分享能为大家的 Excel 数据处理工作带来实实在在的帮助。
评论 (0)