ExcelでUNIXタイムを扱う - KuroNeko666’s blog

仕事で扱う機会があったが、忘れていたので備忘(……だったんだけど、いろいろ追記しまくりで、ひとつの説明資料になっているかも)。

Excelのシリアル値をUNIXタイムへ変換する式と、UNIXタイムをExcelのシリアル値へ変換する式の二つ。
Excel上での変換式（セルへ入力する関数）は、以下のとおり。

※Linuxタイムも、UNIXタイムに準じる。

■ Excelの日付（シリアル値）をUNIXタイムへ

セルに入力する式
=(DATE(yyyy,m,d)-DATE(1970,1,1)-9/24)*60*60*24
yyyy,m,d は、それぞれ適切な値（年月日）を入れること。
DATE コマンドはシリアル値を返す関数なので、Excel の Cell に日付がシリアル値で入っているなら、そのまま参照する。

意味は、
~~現在日付~~ 指定した日付（DATE(yyyy,m,d)）から、UNIXタイムの起点である日付（DATE(1970,1,1)）を引き、UTC(Univarsal Coordinated Time)とJST-9との時差（9時間=1/24*9=9/24）を引く。
さらに、24時間（60秒x60分で1時間。1時間x24で1日）を掛けてあげる。

今日の日付を計算するならば…
2007.8.21=39315
1970.1.1=25569
60*60*24=86400
(39315-25569-9/24)*86400=1187622000

よって、UNIXタイムでは 1187622000 と表現する。

■ UNIXタイムをExcelの日付（シリアル値）へ

UNIXタイムへ変換する式の逆。
=(DATE(yyyy,m,d)/60/60/24)+25569+9/24

UNIXタイムを24時間で割ってから、不足する日時と時差を足してあげる。

〜2008年12月3日追記〜
たぶん、上記の式だと意味がわからない。
自分で書いてて、意味を理解するのにちと時間がかかった(^_^;
「そのまま並べ直すと、こうなるよ」…っていう、考え方をそのまま書いただけだ。

蛇足説明をすると…
UNIXタイムが 1187622000 の場合に、60で割ると単位が "分" に変わる。
1187622000秒 → 19793700分

"分" の単位を 60 で割ると "時間" の単位に変わる。
19793700分 → 329895時間

1日は24時間なので、24 で割ると、単位が "日" に変わる。
329895時間 → 13745.625日

この時点で一応シリアル値の形式(1日)にはなったけど、このままでは「UNIXタイムの起点」と「Excelのシリアル値の起点」までの差分、日数が足らない。
そのため、(閏年を含ませて 25569日になる) 日数を足してあげる。

=1/24*9 が、JST の時差 9:00 のシリアル値を現わすので、縮めて =9/24 を足してあげる。
すると、UNIXタイム (UTC) が Excel のシリアル値 (JST-9) に置き換わる。

=(/60/60/24)+25569+9/24

サンプルを書くと…

=(1234567890/60/60/24)+25569+9/24

この式を入れると、小数点を含むシリアル値が表示される。
備考にもちょっと書いたけど、Excelのシリアル値のままでは分かりにくいので、書式設定はきちんと設定すること。
サンプルで表示されるのは 2009/2/14 8:31:30 となるはず。

もっと簡単なサンプルにすると…

=(1234567890/86400)+25569.375

となる。

質問をいただいたため、さらに追記する。
A1 に、サンプル 1234567890 を入力。
B1 に、=(A1/60/60/24)+25569+9/24 を入力。

こんな感じで、シリアル値が表示される。

下の図は、とりあえずTEXT関数で表示形式を整えたもの。
B2 に、=TEXT(B1,"YYYY/MM/DD hh:mm:ss") を入力している。

■ シリアル値に関する簡単な説明

シリアル値とは、Excel 内部で利用される日時を示す数値。
1900年1月1日を基点として、1日ごとに1ずつ増える。
シリアル値が 1 なら 1900年1月1日 00:00:00 となる。
シリアル値が 2 なら 1900年1月2日 00:00:00 となる。
ついでに書くと、シリアル値が 0 なら内部的に1900年1月0日になっていて、たとえば単純に 9:00 を現わすなら内部的には 1900年1月0日 9:00 になる。

1時間は1を24で割った小数点以下の値がひとつの単位。
1分は1を24で割って60で割った小数点以下の値がひとつの単位。
1秒は1を24で割って60で割って60で割った小数点以下の値がひとつの単位。

40000.1234567 だと 2009年7月6日 2時57分47秒になる。

■ UNIXタイムに関する簡単な説明

UNIXタイムとは、UNIX/Linux 内部で利用される日時を示す数値。
1970年1月1日を基点として、1秒ごとに1ずつ増える。
UNIXタイムが 0 なら 1970年1月1日 00:00:00 となる。
UNIXタイムが 1 なら 1970年1月1日 00:00:01 となる。
→ 今回の計算式では時差が考慮されているので、1970年1月1日 09:00:00 が表示される。

〜追記はここまで〜