とあるJavaプログラムで、イベントが発生した日時をシリアル値で記録したデータファイルがありました。
データを解析しようとJavaであれこれやっていたのですが、Excelの方がいいなと思い、シリアル値をExcelのワークシートに読み込み、処理しようとすると。。。うまくいきません。調べると起点が違うからでした。
Javaで得たシリアル値はいわゆるUNIX時間で1970年1月1日 0:0:0からの経過ミリ秒数をカウントした値です。
一方、Excelの方は、1900年1月1日を1とした値で、1日過ぎた1900年1月2日であれば2になるという感じです。
変換するには。。。たくさんの方がこれまでブログ等に書かれていて、それらを参照しながら、自分の理解をまとめてみました。車輪の再開発になりますが。。。ご容赦を。
まず、答えを先に書きます。
A2にJavaで取得したシリアル値を入れます。
変換するための式は上図の通りです。説明します。
シリアル値はミリ秒なのでExcelに合わせて日オーダーに変換するため、シリアル値を（1000ミリ秒 x 24時間 x 60分 x 60秒）で割ります。
そのあとE3の項目を足していますが、これは、UNIX時間の起点とExcelの起点の日数を計算した答えで、UNIX時間にこれを加えることでExcelの日付と同じに扱えるようになります。
また、「+1」がありますが、これが最初わからなかったのですが、上記の通りで、Excelのシリアル値は1900年1月1日が1なんですね。０じゃない。なので１加えています。
最後に「9/24」がありますが、これは日本時間とGMTとの差になります。これは直ぐにわかると思いますが、なぜ「-9/24じゃないのか」が腑に落ちませんでした。
詳しくは以下に書かれていて、とてもわかりやすく書かれているのですが。
[Groovy|Java]タイムゾーンに関するメモ - Qiita
ちなみに、Excelの時間はパソコンで設定したタイムゾーンを起点として計算されます。その上でJavaになります。
Javaのシリアル値はUNIX時間であり、これまでタイムゾーンを意識したことがなかったのですがGMTなのだということです。つまり、日本のタイムゾーンで1970/1/1 0:0:0のシリアル値を取得すると（deprecatedな使い方ですが例外とか面倒なのでごめんなさい）。。。-32400000という答えが返ってきます。
これは-32400000 = -(9 * 60 * 60 * 1000)となります。日本時間はGMTより＋９時間ですから、この結果がマイナスになるのが直感的に理解できませんでした。上記のサイトの説明を読み理解したところでは、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時間はあくまで１つなので。。。2018年に日本にいる私がJavaでプログラムを作って日本における「1970年1月1日 0時0分0秒」のときのUNIX時間って？と考えると。。。日本の時計はGMTより9時間分多いんだからGMTを取得するには9時間分引かないといけないね。。。
というなんともアホらしい想像をしてしまうのでした。
とりあえず上記で計算できることを確かめています。間違いはないと思います。ただ、上記の説明には怪しいところが多々あると思います。まだしっくりきていません。
簡便に計算するには以下の式でもいいと思います。
