32
31

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

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

Last updated at Posted at 2014-03-03

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のシリアル値(日付・時間)に変換する方法

32
31
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
32
31

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?