時にはパソコン、時にはバイク、趣味まるだしの今回は…
■2007.8.21 (火)
ExcelでUNIXタイムを扱う
仕事で扱う機会があったが、忘れていたので備忘。
Excelのシリアル値をUNIXタイムへ変換する式と、UNIXタイムをExcelのシリアル値へ変換する式の二つ。
Excel上での変換式（セルへ入力する関数）は、以下のとおり。
■ Excelの日付（シリアル値）をUNIXタイムへ
セルに入力する式
=(DATE(yyyy,m,d)-DATE(1970,1,1)-9/24)*60*60*24
yyyy,m,d は、それぞれ適切な値（年月日）を入れること。
意味は、
現在日付 指定した日付（DATE(yyyy,m,d)）から、UNIXタイムの起点である日付（DATE(1970,1,1)）を引き、UTC(Univarsal Coordinated Time)とJST-9との時差（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タイム>/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ずつ増える。
シリアル値が 2 なら 1900年1月2日 となる。
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タイムが 2 なら 1970年1月1日 00:00:01 となる。
→ 今回の計算式では時差が考慮されているので、1970年1月1日 09:00:01 が表示される。
〜 追記はここまで 〜
■ 備考
ちょっとした注意事項
- 選択したセルで、[Ctrl] + [;] キーを押すと、今日の日付が入力される。
- シリアル値に変換するには、右クリックするとでるコンテクストメニューの「セルの書式設定」で「表示形式」を標準にする。
- シリアル値のままでは分かりにくいので、Excelに入力したらセルの書式設定で日付表示をきちんと整えること。
■ 蛇足
一般に使わないけど「神武天皇即位紀」という暦がある。
西洋文化を取り入れることに執着した明治政府が制定した暦だ。
略して"皇紀"の算出方法は、西暦に660年を加える。
よって、今年は皇紀2667年。
キリストが生まれる660年も前に、神武天皇は即位したんだねぇ…
Permalink | コメント(2) | トラックバック(1) | 18:37