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

  • 19
    Like
  • 2
    Comment
More than 1 year has 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のシリアル値(日付・時間)に変換する方法