[Excel] 將四位數字自動轉為時間格式 - Nothing but..

最近在幫忙處理一些Excel 的工作時數表，碰到了幾個需求。

正常的時間格式如：15:00，大家都很懶得多按Shift 打那個冒號，所以記錄個人工作時數的 ... Skiptocontent 15March2020 / wade / Office [Excel]將四位數字自動轉為時間格式 最近在幫忙處理一些Excel的工作時數表，碰到了幾個需求。

正常的時間格式如：15:00，大家都很懶得多按Shift打那個冒號，所以記錄個人工作時數的時候，通常都只用4位數字，例如：「0800-1700」，這個情況下，會讓彙整工時的人員有點麻煩，因為Excel似乎還是要需是正規的時間格式，才能去做時間計算或時數的加總，因此辛苦的行政人員總是人工計算時數… 下面就來說明一下Excel在這個問題上的處理方法 輸入4位數字自動顯示為時間格式 想要輸入1500就自動變成15:00，只要在格子上按右鍵，點選「儲存格格式」 在左邊的「類別」中選擇「自訂」並在「類型」中填上：00":“00 按下確定後，在該格內輸入1500，就會自動顯示為15:00了 計算時數 格式為時間的時候，Excel可以直接用儲存格相減來算出時數，但由於前一步驟裡，儲存格的格式是自訂的，因此它並不是Excel所認識的正規時間格式。

儲存格表面上看是時間格式的「15:00」，但我們只要點擊該儲存格，在上面的值欄位裡面顯示的其實是我們偷懶下所輸入的「1500」 因此在上面的例子中，C2的16:00減去B2的15:00，如果D2的格式為通用格式，最後得到的數值會是100，也就是1600-1500的結果 如果將D2設為時間格式，出現的結果也會是00:00，完全不是我們想要的工時 既然我們輸入的時間並非正規的timeform，因此在計算上也得用到其它的函式來達成我們想要的時間計算，下面函式即可把四位數字換算為時間格式： (ROUNDDOWN(INPUT,-2)/2400+MOD(INPUT,100)/1440) 以上面的例子來看，我們希望時數D2是結束時間C2減去開始時間B2的相減結果，那麼首先我們先將D2的儲存格格式設為「時間」，至於時間的呈現方式就依自己的需求，本例中時間的格式使用「13:00」 接著在D2中，填入下面的函式就可以達到我們想要的時間相減 =(ROUNDDOWN(C2,-2)/2400+MOD(C2,100)/1440)-(ROUNDDOWN(B2,-2)/2400+MOD(B2,100)/1440) 時數的加總計算 以下圖為例，橙色的格子是我們的工時加總，函式為： SUM(D2:D9) 在3/1~3/3日，每天的工作時間為9小時，因此總工時應為27小時，但橙色的加總欄位呈現的卻是「3:00」，這是因為現實中，並不存在「27:00」這種時間，因此Excel自動將該時間除與24，將計算後的餘數作為結果，因此我們如果想要算的是時數的加總，必需要在時數加總的格子中另外自訂格式 首先，一樣在時數加總的格子中按右鍵，點選「儲存格格式」 左邊的類別選擇「自訂」，接著右邊的類型填入：[hh]:mm;@ 按下確認離開後，欄位即可確實的將所有的時數加總計算 本文可同時適用於LibreOfficeCalc與GoogleSheet 參考資料： Excel-輸入時間不輸入「：」(MID,TIME) TimeConversion–Howtoconvert24hrinputas4-digitnumberintoTIMEinExcel? Categories Office Tags Excel LibreOffice Office Sheet Writtenby:wade 本站站長