編按:
非標準格式的時間不能直接加減獲得時長,必須轉換成標準格式後才行。但是怎麽才能把非標準時間如1230轉成標準時間12:30呢?就看你怎麽用TEXT函式了。
今天和大家討論非標準格式時間計算,如飛機飛行時間計算,如下圖所示。
數據中用四位數位表示時間,例如1245的意思是12:45。
只要將數據轉換為標準格式時間,則可用到達時間-起飛時間計算飛行時間。
想象中用公式=TEXT(B2,"00:00")-TEXT(A2,"00:00")就可以,實際卻是這樣的:
掃碼進群,下載Excel課件,同步操作練習。
為什麽會這樣?逐個檢查發現「=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)
完美解決!
關於非標準格式的飛機飛行時長計算就說到這。
最後,歡迎加入Excel函式訓練營,學習68個函式、包含練習課件、輔導答疑。