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

# 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で変換してみる

```js
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年がうるう年になっている為。
(互換性維持の為らしいです。コメントで教えていただきました。)

尚、うるう年の数え方は [別の記事](http://qiita.com/manuluu/items/2f7d3b4420257c41de9a) に書いた。
あっちの記事はこの記事の為に書いた。何か、無駄に長くなったけど。

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:システム時刻](http://ja.wikipedia.org/wiki/%E3%82%B7%E3%82%B9%E3%83%86%E3%83%A0%E6%99%82%E5%88%BB)でも精度は「?」となっている。

しかし、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](http://d.hatena.ne.jp/mindcat/20091108/1257677412)
- [JavaScriptのDateで表現可能な過去と未来Add Star](http://d.hatena.ne.jp/mindcat/20100425/1272203150)


#参考にさせていただいたページ
[UNIX Time から EXCELのシリアル値（日付・時間）に変換する方法](http://asahi.gr.jp/1668)