JavaScript
Excel
VBA

Excelの日付(シリアル値)についてと、UNIX時間との変換 (JavaScript, VBA)

More than 3 years have passed since last update.


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)

までです。

参考:

- JavaScriptの大きな数と小さな数の仕組みを理解する ~ IEEE754入門 ~Add Star

- JavaScriptのDateで表現可能な過去と未来Add Star


参考にさせていただいたページ

UNIX Time から EXCELのシリアル値(日付・時間)に変換する方法