怎樣轉換 Excel 日期時間成 Unix 時間戳記 (unix timestamp)

日期：
從資料庫匯出的時間欄位資料，有很大機會是 Unix 時間戳記 (unix time, unix timestamp) 格式。如何在 Excel 或 Google 試算表將 Unix 時間戳記轉成日期時間、或將日期時間轉成 Unix 時間戳記？



問題狀況

依據維基百科解釋 UNIX時間 是「從UTC1970年1月1日0時0分0秒起至現在的總秒數」。如果想要從 Excel 直接將 Unix 時間戳記轉換成日期時間，欄位值反而會變成「######」。在 Excel 要轉換數值成日期時間，需要是 datavalue 序列值。根據微軟 DATEVALUE 函數 - Office 支援 文件說明「Excel 會以連續的序列值儲存日期，以便將日期用於計算。 根據預設，1900 年 1 月 1 日是序列值 1」。

轉換公式1： Unix 時間戳記 轉成日期時間

原本網路教學文件 在 Excel 裡轉換 Unixtime 格式 提到的轉換公式，是將 Unix 時間戳記轉成 datevalue 序列值，需要手動將儲存格格式從通用格式改成詳細時間格式。如果要從 Unix 時間戳記直接轉成日期時間，轉換公式：

世界協調時間 (UTC)
= TEXT( (Unixtime / 86400) + 25569, "YYYY-mm-dd hh:mm:ss")

台灣時區 (UTC + 8)：UTC 時間加上 8 小時
= TEXT( (Unixtime / 86400) + 25569 + 8/24, "YYYY-mm-dd hh:mm:ss")

轉換公式2： 日期時間轉成 Unix 時間戳記

世界協調時間 (UTC)
= (VALUE(日期時間)-25569)*86400

台灣時區 (UTC + 8)：UTC 時間加上 8 小時
= (VALUE(日期時間)-25569 - 8/24)*86400

轉換公式說明


網路教學文件 在 Excel 裡轉換 Unixtime 格式 提到的轉換公式
= Unixtime / 86400 + 25569 + 8/24
教學文件作者提到「86400 是一天的秒數，25569 是從 1900年1月1日 至 1970年1月1日 的天數，記得嗎？Excel是從1900年1月1日起算的！」進一步探討 25569 的天數是如何計算的？

法1: 快解

=VALUE("1970/1/1") 或 =DATEVALUE("1970/1/1")
微軟文件說明 VALUE 函數 「將代表某數值的文字字串轉換成數字。」。而 DATEVALUE 函數 則會「將儲存為文字的日期轉換成 Excel 辨識為日期的序列值。」。

法2: 慢解

如果改用 DATEDIF 函數 計算 1900年1月1日 至 1970年1月1日 的天數
=DATEDIF("1900/1/1", "1970/1/1", "D")

Excel 計算結果是 25568 (線上預覽)。1900年1月1日的序列值1，所以其他日期要計算序列值要改成序列值0起算。也就是 25568 + 1 = 25569。

但是相同函數在 Google 試算表或 LibreOffice Calc 的計算結果卻是 25567 (線上預覽)。實際逐年比較各年的天數，發現 1900 該年天數有差異。Excel 是 366 天，而 Google 試算表是 365 天。在微軟 判斷年份是否為閏年的方法 文件提到：

「因為 Excel 97 之前的 Microsoft Excel 版本僅能處理 1900 年至 2078 年，所以在 Microsoft Excel 中，只有 1900 年符合閏年的 100/400 排除規則。但是，為了與其他的程式相容，Microsoft Excel 將 1900 年視為閏年。」
你可以自行嘗試如果直接改從序列值 0 的日期，計算會發生什麼事？


👉 如果想要直接動手試試看，請前往線上編輯連結

參考資料

圖片素材



留言

張貼留言