非標(biāo)準(zhǔn)格式的時(shí)間不能直接加減獲得時(shí)長(zhǎng)，必須轉(zhuǎn)換成標(biāo)準(zhǔn)格式后才行。但是怎么才能把非標(biāo)準(zhǔn)時(shí)間如1230轉(zhuǎn)成標(biāo)準(zhǔn)時(shí)間12:30呢？就看你怎么用TEXT函數(shù)了。

今天和大家討論非標(biāo)準(zhǔn)格式時(shí)間計(jì)算，如飛機(jī)飛行時(shí)間計(jì)算，如下圖所示。

數(shù)據(jù)中用四位數(shù)字表示時(shí)間，例如1245的意思是12:45。

只要將數(shù)據(jù)轉(zhuǎn)換為標(biāo)準(zhǔn)格式時(shí)間，則可用到達(dá)時(shí)間-起飛時(shí)間計(jì)算飛行時(shí)間。

想象中用公式=TEXT(B2,"00:00")-TEXT(A2,"00:00")就可以，實(shí)際卻是這樣的：

為什么會(huì)這樣？逐個(gè)檢查發(fā)現(xiàn)“=TEXT(B2,"00:00")”就已發(fā)生錯(cuò)誤。

說(shuō)明B2的數(shù)據(jù)無(wú)法按照"00:00"格式顯示。

實(shí)質(zhì)上“00:00”并非Excel可識(shí)別的格式代碼。試試，輸入數(shù)據(jù)如1245，然后CTRL+1自定義格式，格式代碼為“00:00”，確定時(shí)提示不可用。

更換為時(shí)間格式代碼，如“hh:mm”,公式“=TEXT(B2,"HH:MM")”得到的是“00:00”，也不是需要的“12:30”。

任何非時(shí)間的正數(shù)在時(shí)間格式面前都是“年月日 h:mm:ss”形式的數(shù)字。整數(shù)部分對(duì)應(yīng)年月日，小數(shù)部分對(duì)應(yīng)時(shí)分秒。B2中1230在時(shí)間格式面前就是“1903/5/14  0:00:00”，TEXT函數(shù)截取小時(shí)和分鐘得到“00:00”。

看來(lái)此處只能用強(qiáng)了，在冒號(hào)前加感嘆號(hào)，強(qiáng)制顯示冒號(hào)。公式=TEXT(B2,"00!:00")。

結(jié)果正常！再試試=TEXT(B2,"00!:00")-TEXT(A2,"00!:00")：

大部分正確，個(gè)別出現(xiàn)一串#，又是什么情況？

現(xiàn)在單元格都是時(shí)間格式，改成常規(guī)：

#號(hào)的地方都是負(fù)數(shù)，這是航班時(shí)間超過(guò)凌晨12點(diǎn)（跨天）導(dǎo)致的。

為了避免這種錯(cuò)誤，到達(dá)時(shí)間小于起飛時(shí)間的，就要加1（表示1天）。

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

完美解決！

關(guān)于非標(biāo)準(zhǔn)格式的飛機(jī)飛行時(shí)長(zhǎng)計(jì)算就說(shuō)到這。

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育Excel精品好課任你選擇！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

Excel是怎么自動(dòng)識(shí)別日期和時(shí)間的？

TEXT函數(shù)常用套路

自定義格式中的代碼含義和用法

自定義格式讓數(shù)據(jù)以萬(wàn)為單位顯示并保留不同小數(shù)

版權(quán)申明：

本文作者老菜鳥(niǎo)；部落窩教育享有稿件專(zhuān)有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。