Excelの日付について
Excelは「1900年1月0日」を0とする数値を「9999年12月31日」までの各日付に割り振っている。
これを「 シリアル値(Serial Number) 」と呼ぶ。
「シリアル値」は 1900年1月0日からの日数 である。
「シリアル値」なんて呼ぶから「何それ」って感じる。
「シリアルナンバー」と言われれば、各日付に数値が割り振られているだけと分かりやすいと思う。
日付のシリアル値を見るには、セルの書式設定を「日付」でなくデフォルトにする。
- 「 ctrl+shift+^ 」 セルの書式設定を デフォルト にする
- 「 ctrl+shift+3 」セルの書式設定を 日付 にする
- 「 ctrl+@ 」セルの書式設定を 時間 にする
セルに日付の文字列を入力した時点で、自動でシリアル値に変換され文字列ではなくなる。
Excelは、ただの数値であるシリアル値を、日付の書式設定にして表示している。
また、時間は24時間を1とする小数で表現する。
「0.5」は「午前12:00」だし「0.25」は「午前6:00」である。
UNIX時間との変換
Excelのシリアル値は「1900/1/0」からの「日数」である。
UNIX時間は「1970/1/1」からの「秒数」である。ご存じかと思うので、説明は省く。
なので、 日数・秒数を変換し、70年分のズレを±するだけ 。
尚、シリアル値は(日本にいれば)JSTであり、UNIX時間はUTCなので、その時差の9時間も±する。
シリアル値の精度については、ミリ秒までと考えていいと思う。(詳細は下の方)
UNIX時間もミリ秒で扱われることが多い気がする。(JavaScript等)
うるう秒については、UNIX時間ではシステムによって考慮したりしなかったりするらしい。
Excelでは考慮されないので、ここでは考慮していない。
JavaScriptで変換してみる
var COEFFICIENT = 24 * 60 * 60 * 1000; //日数とミリ秒を変換する係数
var DATES_OFFSET = 70 * 365 + 17 + 1 + 1; //「1900/1/0」～「1970/1/1」 (日数)
var MILLIS_DIFFERENCE = 9 * 60 * 60 * 1000; //UTCとJSTの時差 (ミリ秒)
function convertUt2Sn(unixTimeMillis){ // UNIX時間(ミリ秒)→シリアル値
return (unixTimeMillis + MILLIS_DIFFERENCE) / COEFFICIENT + DATES_OFFSET;
}
function convertSn2Ut(serialNumber){ // シリアル値→UNIX時間(ミリ秒)
return (serialNumber - DATES_OFFSET) * COEFFICIENT - MILLIS_DIFFERENCE;
}
function dateFromSn(serialNumber){ // シリアル値→Date
return new Date(convertSn2Ut(serialNumber));
}
function dateToSn(date){ // Date→シリアル値
return convertUt2Sn(date.getTime());
}
70年分の日数のズレ
70(年) × 365(日) + 17(70年分のうるう年の分の日数)。
シリアル値が「1900/1/0」という存在しない日付で始まる為、+1。
(0を日付書式にすると「1900/1/0」と表示される。1899/12/31とも言われる)
あとの+1は、本来はうるう年でない1900年がうるう年になっている為。
(互換性維持の為らしいです。コメントで教えていただきました。)
尚、うるう年の数え方は 別の記事 に書いた。
あっちの記事はこの記事の為に書いた。何か、無駄に長くなったけど。
Mac版のExcel等は1904年から始まるらしいけど、4年違うだけなのでDATE_OFFSETの70を66にするだけだと思う。試してないけど。
VBA (2016/03/17追記)
VBAのコードも載せてましたが消しました。ごめんなさい。
VBAのコードが欲しい場合は他の記事を参考にするか、自分で作成するか、過去の編集履歴をご覧ください。
消去理由：
・ 64bitのExcelでしか使えないLongLong型を使用していた為
・ 32bitではLongで計算することになるがミリ秒まで計算できない為
・ 秒でも1901年12月15日から2038年1月19日までしか計算できない為
・ VBAへの怒りと憎しみが限界に達し、これ以上は危険だと判断した為
#シリアル値の精度について
「24時間を1とする小数」という素敵な単位である為、どの程度の精度で記録されるのかは謎。
Wikipedia:システム時刻でも精度は「?」となっている。
しかし、Excelの関数ではミリ秒までしか記録/取得できない。
なので、精度はミリ秒としていいんではと思う。
しかも、ミリ秒まで扱える関数はあんまり無い。
環境にもよるかもしれないが、自分の環境(Windows 8.1 + Excel 2013)では
- TIMEVALUE() ミリ秒まで取得可能、ミリ秒以上指定すると切り捨て。
- TEXT() ミリ秒まで変換可能、ミリ秒以上指定するとエラー。
- NOW() 10ミリ秒まで記録。
という感じ。他の関数では全て切り捨てられる模様。
##ミリ秒精度でシリアル値を扱える関数
(2016/3/17追記： 環境によっても違うかも。たしかWindows 8とExcel 2013 64bitで動作確認した)
★TIMEVALUE()
日付文字列をシリアル値に変換する関数。
例: TIMEVALUE("2112/9/3 12:34:56.789")
ミリ秒以下の数字を入れてもシリアル値に変化は無かった為、精度はミリ秒。
(これ使わなくても、セルに文字列を入力すると勝手にシリアル値になる。その場合も精度はミリ秒まで)
★TEXT()
数値を文字列に変換する関数で、シリアル値も変換できる。
出力するフォーマットを文字列で自由に決められる。
例: TEXT(NOW(), "yyyy/mm/dd hh:mm:ss.000")
ミリ秒以下まで指定すると「#VALUE!」のエラーとなる為、精度はミリ秒。
★NOW()
現在日時のシリアル値を取得する関数。精度は10ミリ秒。
TEXT(NOW(), "yyyy/mm/dd hh:mm:ss.000")と入力してF9キーを押したままにするとよく分かる。
JavaScriptの整数計算の誤差について (2014/3/15追記)
JavaScriptは数値を全て浮動小数点数で扱っています。
精度が保証されているのは自然数で
「9007199254740992」
までです。それ以上だと誤差が出る可能性があります。
現在のUNIX時間のミリ秒で
「1394862916722」 (2014/3/15 14:55:16)
なので、3桁ほど余裕があり、誤差は大丈夫なハズ。
ちなみに、Dateの最大値は
「8640000000000000」 (275760/9/13 00:00:00)
までです。
参考:
#参考にさせていただいたページ
UNIX Time から EXCELのシリアル値（日付・時間）に変換する方法