意外と知らない「日付」のこと

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

下図のようなデータがあります。

この中から、特定の日付を探してみましょう。件数が少ないので、1セルずつセルの値を調べます。

Sub Macro1()
    Dim i As Long
    For i = 1 To 5
        If Cells(i, 1).Value = "2020/12/10" Then
            MsgBox Cells(i, 1).Address
            Exit Sub
        End If
    Next i
    MsgBox "見つかりません"
End Sub

上記のコードを実行すると、問題なく見つかります。

では、次のコードではどうでしょう。

Sub Macro2()
    Dim i As Long
    For i = 1 To 5
        If Cells(i, 1).Value = "2020/12/9" Then
            MsgBox Cells(i, 1).Address
            Exit Sub
        End If
    Next i
    MsgBox "見つかりません"
End Sub

実行すると、こうなります。

いかがでしょう。なぜ、こうなるのか、あなたは正しく認識していますか？そして、じゃ、どーしたらいいかを理解していますか？今回は、こんな簡単なことなのに、意外と知られていない日付データについて解説します。

数式バーの表記

まずは、素朴な疑問です。下図をご覧ください。

セルD1には「1234」という値が入力されています。このセルD1に「#,##0円」という表示形式を設定しました。表示形式は、セルの"見え方"を設定する仕組みです。数式バーに注目してください。数式バーには、セルD1に入力されている値が表示されています。間違いなく「1234」ですね。だから、このセルD1のValueプロパティを調べると「1234」という"入力されている値"が取得されます。

Sub Macro3()
    MsgBox Range("D1").Value
End Sub

このことから、セル(Rangeオブジェクト)のValueプロパティは、セルに入力されている"値"を返すことが分かります。では、次はどうでしょう。

カンのいい方でしたら、この時点で「ん？」って感じるはずです。今度は、セルD1に日付を入力しました。Excelは日付をシリアル値という仕組みで扱っています。シリアル値とは、1900年1月1日を1として、それ以降、一日経過するごとに1ずつ増える連続した数値です。ちなみにExcelは、9999年12月31日までのシリアル値を持っています。いわば"万年カレンダー"みたいなものですね。Excelは、このシリアル値という仕組みで日付を扱っているから、○日後などを計算できるんです。セルD1に入力されているシリアル値を調べるには、セルの表示形式を「標準」に設定します。

セルD1に入力されている値(シリアル値)は、44175です。この日は、1900年1月1日から起算して44175日目だということですね。でも、人間は44175と言われても、それが何年何月何日なのか分かりません。そこで、この入力されている値(シリアル値)に「yyyy/m/d」などの表示形式を設定して、セルの見た目を「2020/12/10」としているんです。あれ？ちょっと待ってくださいよ。さっきの「1234」と話が違いますよね。数式バーにはセルに入力されている値が表示されるんでしたよね。いまセルD1には「44175」が入力されているはずです。でも、数式バーには、"人間が理解できる"ような「2020/12/10」が表示されています。では、この状態でセルD1のValueプロパティを調べてみましょう。

セルに入力されている「44175」ではなく「2020/12/10」が取得されました。これは、ある意味でMicrosoftの"配慮"です。Excelは日付をシリアル値として扱っています。そうしないと計算できませんから。でも、人間にシリアル値を示したところで意味不明です。だから、日付(シリアル値)が入力されているセルを選択したとき、数式バーには"人間が理解できる"形式で表示されます。その方が嬉しいでしょ。同様に、Valueプロパティで値を調べるときも、結果が「44175」では人間が扱いにくいです。そこで、セルに「日付型」の値が入力されているとき、Valueプロパティは"人間が理解できる"形式を返すようになっています。ちなみに、実際に入力されているシリアル値を取得したいときは、ValueプロパティではなくValue2プロパティを使います。

Sub Macro3()
    MsgBox Range("D1").Value2
End Sub

標準の日付形式

さて、ここまでの解説で、何度も"人間が理解できる"形式、みたいに回りくどい言い方をしてきました。では、この"人間が理解できる"形式とは、いったいどんな形式なのでしょう。これは、セルに設定されている表示形式とは違います。試しに、セルD1の表示形式を変更してみましょう。セルD1を選択して[セルの書式設定]ダイアログボックスを開き、表示形式に「*2012年3月14日」を指定します。その下にある「2012年3月14日」ではありません。先頭に「*」がついている、上から2つめの表示形式です。この「*」の意味は、後で解説します。

[OK]ボタンをクリックすると表示形式が設定されます。

でも、数式バーの表記は「2020/12/10」のままです。数式バーに表示されるのは、表示形式と関係ありません。この状態でValueプロパティを調べると

表示形式とは関係ない形式の日付が取得されます。ここ、重要なポイントですから、しっかり理解してください。セルに「日付型」の値が入力されているとき、セル(Rangeオブジェクト)のValueプロパティは、セルに入力されている値(シリアル値)でも、セルに設定されている表示形式でもなく、標準の日付形式を返すということです。では、標準の日付形式とは何でしょう。実はこれが、冒頭に示した「2020/12/10は見つかるけど、2020/12/9は見つからない」の原因です。試しに、セルD1に「2020/12/9」と入力してみましょう。

数式バーでは、表示形式が反映されていません。数式バーにシリアル値を表示してしまうと、人間が理解できませんから、このシリアル値を標準の日付形式で表しています。しかし、ここからが問題です。このセルD1のValueプロパティを取得してみます。

お分かりいただけただろうか。Valueプロパティで取得されるのは、シリアル値でもなく、表示形式が反映された日付でもなく、ましてや、数式バーに表示されている形式でもありません。Valueプロパティで取得されるのは「yyyy/m/d」ではなく「yyyy/mm/dd」形式なんです。冒頭でまず、セルのValueプロパティが「2020/12/10」かどうかを判定しました。この日付の「yyyy/mm/dd」は「2020/12/10」です。だから一致しました。しかし「2020/12/9」の「yyyy/mm/dd」は「2020/12/09」です。だから「2020/12/9」が見つからなかったんです。ややこしいですから、落ち着いて考えてくださいね。

Valueプロパティが返すのは、日付の短い形式です。では、この"日付の短い形式"とは、いつも変わらず「yyyy/mm/dd」なのかというと、そんなことはありません。実はこの"日付の短い形式"は、Windowsの設定で決まっています。Windows 10でしたら、Windowsの設定で「時刻と言語」を選択します。

表示される画面で「地域」を選択します。画面最下部に、現在設定されている「日付の短い形式」などが表示されています。

上図の「日付(短い形式)」で、「1月」の「1」が「01」となっていることに留意してください。このように、Windowsに標準で設定されている「日付の短い形式」は「yyyy/mm/dd」です。そして、セル(Rangeオブジェクト)のValueプロパティは、この「Windowsに設定されている短い形式」で日付を返してきます。さらに下の「データ形式を変更する」をクリックすると、次の画面が表示されます。

ここで、Windowsの設定である「日付の短い形式」を変更できます。試しに一番下の「2017-04-05」に設定を変えてみます。

Excelに戻ると、次のように表示されます。

自動的に、数式バーの表示が変わりました。つまり、数式バーに表示される日付の形式は、Windowsの設定である「日付の短い形式」だということです。でも、よく見てください。いま設定した「2017-04-05」は「yyyy-mm-dd」です。しかし、数式バーには「yyyy-m-d」形式が表示されます。では、Valueプロパティはどうでしょう。

頭が混乱してきた方のために、ここまでの話を箇条書きでお送りします。

  • 基本的に、セルに入力されている値が数式バーに表示される
  • 基本的に、セルに入力されている値がValueプロパティで取得できる
  • 日付は、セルにシリアル値が入力されている
  • 日付のセルでは、数式バーにシリアル値ではなく「日付形式」が表示される
  • 日付のセルでは、Valueプロパティでシリアル値ではなく「日付形式」が取得できる
  • このとき、数式バーでは「m」「d」形式
  • Valueプロパティでは「mm」「dd」形式
  • 数式バーやValueプロパティで採用されているのは、Windowsの「日付(短い形式)」
  • 「短い形式」を変更しても、数式バーは「m」「d」で、Valueプロパティは「mm」「dd」

ややこしいです。なぜMicrosoftは、数式バーの表記を「m」「d」にしたのかは分かりません。まぁ、おそらく「これ、どーする？」って相談した結果だと思います。ちなみに、Windowsの設定を変えたら、すぐ数式バーの表記も変わりましたが、セル内の表記も変わります。実はExcelの表示形式には、Windowsの設定によって変化するものがあります。それが、先頭に「*」がついた表示形式です。Windowsの設定を変えると、ダイアログボックス内の表記も変わりますし、Ctrl + ;で入力する"現在の日にち"の形式も変わります。

もちろん、表記が変わるのはExcelだけではありません。そもそもが"Windowsの設定"なのですから、たとえばエクスプローラでも表記が変化します。

いずれにしても、Excelは、Excel内だけで完結しているのではなく、Windowsの影響を受ける、Windows上で動いているアプリケーションだということです。さらに、今回の「日付の短い形式」と「日付の長い形式」というのは、Format関数でも使えます。

Sub Macro4()
    MsgBox Format("2020/12/9", "Short Date") & vbCrLf & Format("2020/12/9", "Long Date")
End Sub

言うまでもありませんが、こちらFormat関数の結果も、Windowsの設定によって変化します。

じゃ、どーするのか

いつも私が言っているように、Excelで最も難しいのは「日付と時刻」です。難しい原因のひとつは、必ず「入っているもの(シリアル値)と見えているもの(日付・時刻)が違う」ということです。さらに「どう見せるか」という問題も絡んできます。では、今回のケースみたいに、日付が入力されているセルのValueプロパティを扱うときは、どうしたらいいのでしょう。解説を書くにあたり「はて、私はいつもどうしているだろう？」と考えたのですが、実はあまり困ったことがありません。それは、上記で解説したようなことを、私は知っているからです。Excelが日付をシリアル値で扱うことを知っています。Valueプロパティが「yyyy/mm/dd」形式で日付を返すことを知っています。さらに、その形式はWindowsで設定されているということも知っています。だから

Sub Macro2()
    Dim i As Long
    For i = 1 To 5
        If Cells(i, 1).Value = "2020/12/9" Then
            MsgBox Cells(i, 1).Address
            Exit Sub
        End If
    Next i
    MsgBox "見つかりません"
End Sub

というコードは書きません。つまり、私の経験談でアドバイスするのなら、こうした日付のトラブルを回避する根源的な解決策は、本コンテンツで解説したようなことを理解して、知っているかどうかだと思います。こう言ってしまうと元も子もありませんがｗ しっかりと、ExcelやVBAの基礎を学習することにつきます。決して、必殺技や、ウルトラテクニックや、特効薬や、裏技などはありません。

まぁ、とはいえ、こんなときはこう考えるとできるよ、というやり方をご紹介します。まず、上記の失敗するコードですが、これ、シリアル値で比較したらどうなるでしょう。ちなみに、2020/12/9のシリアル値は44174です。

Sub Macro5()
    Dim i As Long
    For i = 1 To 5
        If Cells(i, 1).Value = 44174 Then
            MsgBox Cells(i, 1).Address
            Exit Sub
        End If
    Next i
    MsgBox "見つかりません"
End Sub

上手くいきます。Valueプロパティは"2020/12/09"を返すのに、なぜシリアル値で成功するのかの理由については、これはまた難しい話になりますので、ここでは割愛します。でも、要するに、シリアル値で指定してやれば上手くいきます。ですが、毎回シリアル値を手で調べるのは大変です。こんなときはDateSerial関数を使いましょう。たとえば、もし年月日の数値が、別々のセルに入力されているとしたら。

こんな感じです。

Sub Macro6()
    Dim i As Long
    For i = 1 To 5
        If Cells(i, 1) = DateSerial(Range("D1"), Range("D2"), Range("D3")) Then
            MsgBox Cells(i, 1).Address
            Exit Sub
        End If
    Next i
    MsgBox "見つかりません"
End Sub

間違っても「Range("D1") & "/" & Range("D2") & "/" & Range("D3")」みたいな発想だけはしないでください。上記のコードでは、DateSerial関数の結果を直接比較していますが、もし一度変数に入れるのなら、格納する変数の型は、Date型でもLong型でも、どちらでもOKです。

Sub Macro7()
    Dim i As Long, A As Date
    A = DateSerial(Range("D1"), Range("D2"), Range("D3"))
    For i = 1 To 5
        If Cells(i, 1) = A Then
            MsgBox Cells(i, 1).Address
            Exit Sub
        End If
    Next i
    MsgBox "見つかりません"
End Sub

Sub Macro7()
    Dim i As Long, A As Long
    A = DateSerial(Range("D1"), Range("D2"), Range("D3"))
    For i = 1 To 5
        If Cells(i, 1) = A Then
            MsgBox Cells(i, 1).Address
            Exit Sub
        End If
    Next i
    MsgBox "見つかりません"
End Sub

もし、検索対象の日付が文字列形式で提供されたら、DateValue関数でシリアル値に変換してください。

Sub Macro8()
    Dim i As Long, A As String
    A = "2020/12/9"
    For i = 1 To 5
        If Cells(i, 1).Value = DateValue(A) Then
            MsgBox Cells(i, 1).Address
            Exit Sub
        End If
    Next i
    MsgBox "見つかりません"
End Sub

日付に関する問題は、まだたくさんあります。みなさんも、悩む機会が多いことでしょう。とにかく「日付は、一筋縄ではいかない」という認識だけは忘れないでくださいね。