memorandums

日々のメモです。

Javaで得たシリアル値をExcelの日付として扱う

とあるJavaプログラムで、イベントが発生した日時をシリアル値で記録したデータファイルがありました。

データを解析しようとJavaであれこれやっていたのですが、Excelの方がいいなと思い、シリアル値をExcelのワークシートに読み込み、処理しようとすると。。。うまくいきません。調べると起点が違うからでした。

Javaで得たシリアル値はいわゆるUNIX時間で1970年1月1日 0:0:0からの経過ミリ秒数をカウントした値です。

UNIX時間 - Wikipedia

一方、Excelの方は、1900年1月1日を1とした値で、1日過ぎた1900年1月2日であれば2になるという感じです。

変換するには。。。たくさんの方がこれまでブログ等に書かれていて、それらを参照しながら、自分の理解をまとめてみました。車輪の再開発になりますが。。。ご容赦を。

まず、答えを先に書きます。

f:id:ke_takahashi:20180119143411p:plain

A2にJavaで取得したシリアル値を入れます。

変換するための式は上図の通りです。説明します。

シリアル値はミリ秒なのでExcelに合わせて日オーダーに変換するため、シリアル値を(1000ミリ秒 x 24時間 x 60分 x 60秒)で割ります。

そのあとE3の項目を足していますが、これは、UNIX時間の起点とExcelの起点の日数を計算した答えで、UNIX時間にこれを加えることでExcelの日付と同じに扱えるようになります。

また、「+1」がありますが、これが最初わからなかったのですが、上記の通りで、Excelのシリアル値は1900年1月1日が1なんですね。0じゃない。なので1加えています。

最後に「9/24」がありますが、これは日本時間とGMTとの差になります。これは直ぐにわかると思いますが、なぜ「-9/24じゃないのか」が腑に落ちませんでした。

詳しくは以下に書かれていて、とてもわかりやすく書かれているのですが。

[Groovy|Java]タイムゾーンに関するメモ - Qiita

ちなみに、Excelの時間はパソコンで設定したタイムゾーンを起点として計算されます。その上でJavaになります。

Javaのシリアル値はUNIX時間であり、これまでタイムゾーンを意識したことがなかったのですがGMTなのだということです。つまり、日本のタイムゾーンで1970/1/1 0:0:0のシリアル値を取得すると(deprecatedな使い方ですが例外とか面倒なのでごめんなさい)。。。-32400000という答えが返ってきます。

f:id:ke_takahashi:20180119151447p:plain

これは-32400000 = -(9 * 60 * 60 * 1000)となります。日本時間はGMTより+9時間ですから、この結果がマイナスになるのが直感的に理解できませんでした。上記のサイトの説明を読み理解したところでは、getTime()した結果はUNIX時間であり、UNIX時間はGMTで1970/1/1 0:0:0からの経過秒数なのだから、日本で1970/1/1 0:0:0であったときのUNIX時間は-32400000した値になる、ということになります。

特に下線部を直感的に理解するのが難しいですね。。。

地球儀を思い浮かべて。。。グリニッジ天文台に時計を持った人かいて、その人が「今、1970年1月1日 0時0分0秒になったから時計を開始するよ」と言ったとします。そして、日本にいる私が日本で1970年1月1日 0時0分0秒を迎えたときに、今、UNIX時間はいくつ?と尋ねると「32400000」だよと答えてくれます。でも、それじゃ、日本の日時は9時間少なくなっちゃうから、日本では日本で1970年1月1日 0時0分0秒から開始するよ。。。と日付の処理がされます。でも、UNIX時間はあくまで1つなので。。。2018年に日本にいる私がJavaでプログラムを作って日本における「1970年1月1日 0時0分0秒」のときのUNIX時間って?と考えると。。。日本の時計はGMTより9時間分多いんだからGMTを取得するには9時間分引かないといけないね。。。

というなんともアホらしい想像をしてしまうのでした。

とりあえず上記で計算できることを確かめています。間違いはないと思います。ただ、上記の説明には怪しいところが多々あると思います。まだしっくりきていません。

簡便に計算するには以下の式でもいいと思います。

Excelのシリアル値=UNIX時間/86400000+25569.375