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函數常用套路

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

自定義格式讓數據以萬爲單位顯示並保留不同小數

版權申明：

本文作者老菜鳥；部落窩教育享有稿件專有使用權。若需轉載請聯繫部落窩教育。