2023/01/11

日付の基準値（セルとVBAの比較）




Excel内では日付をシリアル値という数値で管理しています。ワークシートのセル上では、そのシリアル値が日付の書式に変換され、我々の目には日付として見えています。そのシリアル値の起点は「1900年1月1日を１」としています。
一方VBAの中でも日付は数値で管理されていますが、こちらは「1899年12月30日をゼロ」としています。
つまり、ワークシート管理の日付とVBA管理の日付の起点が１日ズレています。

今回は、ワークシート上とVBA上での日付の起点付近を確認するのと同時に、それぞれの「日付の範囲」と、小数点以下で表される「時刻」についても見ていきます。

１．日付

図１では、１行目に数値を並べています。そしてその値を使って、２行目に「日付書式で表示」し、３行目は「VBAで日付変換」しています。よって２行目が「ワークシート上の日付」、３行目が「VBAの日付」となります。
数値～日付の対照表
図１

図１の３行目に使ったユーザー定義関数を図２に示します。引数として１行目の数値を受け取り、日付の文字列を戻します。
  1. '==========　⇩(1)　VBAの日付を戻す関数　============
  2. Public Function CD(d As Double) As String
  3. 　CD = Format(d, "yyyy/mm/dd")
  4. '　CD = Format(d, "yyyy/mm/dd hh.mm.ss")
  5. End Function
図２

「ワークシート上の日付のシリアル値の起点（＝１）」は、図１のF2セルの「1900年1月1日」になります。また「VBAの日付の起点（＝ゼロ）」は、図１のE3セルの「1899年12月30日」です。この時点では、１日ズレているのが分かります。

と言って、現在もズレ続けている訳では無く、図１のK列の「1900年3月1日」以降は「ワークシート上とVBAの日付値は同じ」になります。ズレを吸収しているのは、J2セルである「1900年2月29日」です。但し、この1900年は「うるう年」ではありません。
この起点が異なる原因は、遡ればロータス１－２－３の設計時に「1900年はうるう年だと勘違いした」とか「1900年をうるう年とすると計算が楽だった」など、色々な説があるようです。

そして、この「日付＝数値」がいつまで続くかと言うと、図１のL列で示した「9999年12月31日まで」使えることが分かります。この上限はワークシートもVBAも同じです。

下限側についてです。
ワークシート日付については、シリアル値＝１の起点である「1900年1月1日（図１のF2セル）」です。その１日前の「シリアル値＝ゼロ」は、表示が「1900年1月0日」となってしまいます。計算的には「1899年12月31日」と同等と思われますが、使用しない方が良いかと思います。
また、シリアル値の上限下限を超えると、表示は図１のM2セル・D2セルの様に「#印の連続」となります。

VBA日付については図１のD列・C列で分かるように、マイナスの値でも計算をしてくれて、下限は「西暦100年1月1日」となります。上限下限を超えるとエラーとなります。

２．時刻

Excel・VBAでは「１日間＝数値１」です。１日の中の時刻は「小数点以下」で管理していて、24時間で１となります。
例えばVBA日付値で、数値＝1 は「1899年12月31日（の午前0:00）」なので、「数値＝1.5」は「1899年12月31日の昼12:00」です。これは時間の流れに沿っているため、すぐに計算できます。
しかし値がマイナスになるとやっかいです。時間の流れも併せて示すと、図３のようになります。
数値～日付の対照表
図３

例えば数値＝-1 は、図１の表でも説明した通り「1899年12月29日（の午前0:00）」になりますが、その「1899年12月29日」という日付は以下のような範囲となります。
　-2 < 1899年12月29日 <= -1
そのため「数値＝-1.5」は、図１だけを見ていると時間の流れで「前日の1899年12月28日の昼12:00」と考えてしまいそうですが、実際に計算してみると「1899年12月29日の昼12:00」となります。
また -1超～ゼロの間は、ゼロ以上～1未満の「1899年12月30日」と同じで、時間の流れが逆になっています。

そのため、図４のように「ゼロを跨ぐような日時の計算」をするときには注意が必要です。
  1. '==========　⇩(2)　日付の加減算　============
  2. Sub test1()
  3. 　Const Day1 As Date = "1899/12/30 6:00:00"
  5. 　MsgBox Format(Day1 - 1, "yyyy/mm/dd hh.mm.ss")
  6. 　MsgBox Format(DateAdd("d", -1, Day1), "yyyy/mm/dd hh.mm.ss")
  7. End Sub
図４

１４行目では「Format(Day1 - 1, "yyyy/mm/dd hh.mm.ss")」を計算し、その結果をメッセージ表示しています。「1899/12/30 6:00:00」は数値で表すと「0.25」ですので、１日間を引き算すると「0.25 - 1 = -0.75」となり、日付で表すと「1899/12/30 18:00:00」と逆に6時間進んでしまう結果になります。
一方１５行目では「Format(DateAdd("d", -1, Day1), "yyyy/mm/dd hh.mm.ss")」と「DateAdd関数」を使用して日にちの加減算をしています。この関数を使用すると、正しい「1899/12/29 6:00:00」という結果が得られます。

また、図５のようなゼロを跨いだ日数計算でも同じです。
  1. '==========　⇩(3)　日数計算　============
  2. Sub test2()
  3. 　Const Day1 As Date = "1899/12/31 6:00:00"
  4. 　Const Day2 As Date = "1899/12/29 6:00:00"
  6. 　MsgBox (Day1 - Day2)
  7. 　MsgBox DateDiff("d", Day2, Day1)
  8. End Sub
図５

２５行目では「Day1 - Day2」と、「1899/12/31 6:00:00」～「1899/12/29 6:00:00」の間の日数を計算しています（MsgBoxの引数をカッコで括っているのは、分かり易くする為です）。「２日間」が正解ですが、２５行目での結果は「2.5日」です。各日付を数値で表すと「1.25」と「-1.25」だからです。
一方２６行目の「DateDiff("d", Day2, Day1)」は、DateDiff関数で日数差を計算していますが、正しい「2日」が得られます。

なおVBA日付に於いて、小数点以下の時刻込みの日付値をワークシート上で確認する場合は、ユーザー定義関数（図２）の０３行目（見え消し行）を使用して下さい。「日付＋時刻」を表示する事が出来ます。

３．まとめ

以上を整理すると図６のようになるかと思います。
ワークシート日付VBA日付
使用範囲1900/1/1 ～ 9999/12/31西暦100/1/1 ～ 9999/12/31
起点1900/1/1 = 11899/12/30 = 0
数値1900/03/01（値=61）以降は同じ値
時間
軸		ﾌﾟﾗｽ側連続
ﾏｲﾅｽ側－不連続
図６

値がマイナスの時（1899/12/30 以前）には、時間軸がプラス側とは逆を向くために、時刻を含めた日付計算時には日付に特化した関数（DateAdd や DateDiff など）の使用をお勧めします。但し西暦1899年は「明治３２年」に当たりますので、実用上は問題無いのかもしれません。

アプリ実例

「Excelシート上にDBを作り、SQLを使ってデータを入出力する」
「月の最終日の計算方法」
「ブックを開いた時に自動的にバックアップを取る」
「ExcelシートDBを使った会議室予約システム」
「２つの日付間の平日の日数計算」
「CSVファイルでデータを読み書きする月間予定表」
「条件付き書式で作る月単位シフト表」
「シート内検索結果を色付け表示」
「祝日を自動反映するカレンダー」
「〇月〇日と入力した日付の年を指定」
「共有コメント付きカレンダー（固定長ファイルを使用）」
「ボタンを自動生成するフォームカレンダー」

サンプルファイル

日付の基準値（its-024.xlsm）
セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始）
解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。