當前位置: 妍妍網 > 辦公

Excel教程:如何把1230變成時間12:30?90%的可能你會弄錯!

2024-06-17辦公

編按:

非標準格式的時間不能直接加減獲得時長,必須轉換成標準格式後才行。但是怎麽才能把非標準時間如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個函式、包含練習課件、輔導答疑。