なにぬねのーつ

No.51 DATEDIFの謎を追え(2) 〜 DATEDIF関数の実装(VBA編)
公開日 2007/12/30 キーワード EXCEL, VBA, DATEDIF 対応バージョン 添付ファイル DATEDIF123_SAMPLE.zip
前回ではEXCElのDATEDIF関数とそのオリジナルであるロータス1-2-3の@DATEDIFについてテスト結果を比較しいったい何が問題なのか?本来DATEDIF関数はどういった結果になるべきなのについて検討してきました。今回はその結果を踏まえてDATEDIF関数の本来あるべき姿をVBAで実装したいと思います。

ところでVBAにはFが1つ多いDATEDIFFという名前の関数が存在します。DATEDIF関数と似ているようで似ていない紛らわしい関数です。全く別物の考えた方が無難で、例えば年数をカウントする"yyyy"オプションでは1日でも年が変われば1年とカウントします。"y"オプション(および"d"オプション)は年数ではなく通算日数をカウントします。

以下がVBA版のDATEDIF関数になります。ロータス1-2-3の@DATEDIFと完全互換があります。なおDATEDIFFと区別するため便宜上名称をDATEDIF123としました。
(2008/1/2) エラーケースを追加しました。

解説

月数(M)の計算がミソになります。月数さえ求まればおのずと他のオプションも求まります。
最初に思いつくのは以下のような計算だと思います。これは開始日の年月と終了日の年月から概算月数を求め、開始日の日より終了日の日が大きい(満了日に達していない)場合は1を引くというものです。
<案1>日付による比較
Case "M": ' 月数
    x = (y2 * 12 + m2) - (y1 * 12 + m1) ' 年月を月数に直して概算
    If d1 > d2 Then
        x = x - 1
    End If
これだと前回のテストケース3と4を満たすことはできません。
それでは開始日の年・月を終了日の年・月にシフトする方法はどうでしょうか。
<案2>開始日の年・月をシフト
Case "M": ' 月数
    x = (y2 * 12 + m2) - (y1 * 12 + m1) ' 年月を月数に直して概算
    t = DateSerial(y2, m2, d1)
    If DateDiff("d", t, endDate) < 0 Then
        x = x - 1
    End If
残念ながらこれも結果は<案1>と同じです。
これは面倒な計算になるかなと思いましたが、ふと思いついたのが以下のアルゴリズムです。
<案3>DATEADDの利用
Case "M": ' 月数
    x = (y2 * 12 + m2) - (y1 * 12 + m1) ' 年月を月数に直して概算
    t = DateAdd("m", x, startDate) ' 開始日からxヵ月後
    If DateDiff("d", t, endDate) < 0 Then
        x = x - 1
    End If
回りくどいだけで同じような処理に見えますが、DATEADD関数を利用することで開始日を月数だけ正確にシフトすることができます。2007/1/31の1ヶ月後は<案2>で使用しているDATESERIAL関数の場合2/31→3/3となりますが、<案3>のDATEADDの返す日付は末日である2/28になります。よって前回のテストケース3と4を満たすことができます。

DATEDIF123の使い方(ユーザー定義関数)

このDATEDIF123関数の使い方ですが「ユーザー定義関数」として使用することをお勧めします。EXCEL標準のDATEDIF関数とまったく同様に取り扱うことができます。

VBEで上記のDATEDIF123関数を標準モジュールとして作成しておきます。
メニューから[挿入]-[標準モジュール] ※EXCEL2007の例
メニューから[挿入]-[標準モジュール] ※EXCEL2007の例

EXCELに戻りワークシート上で関数を挿入します。ダイアログボックスで関数分類をユーザー定義にします。下の関数名にDATEDIF123が出てくれば正解です。
関数の挿入

なお、常用する場合はユーザー定義関数を個人用マクロブック(PERSONAL.XLS)に保存すると便利ですが、ここではあえてこれ以上は説明しません。

まとめ

最初にエラーケース(開始日と終了日が逆転)を処理していますが、これは1-2-3の@DATEDIFに合わせたもので開始日、終了日が同一年月日を境にして反対称の結果になります。(EXCELのDATEDIFではエラー(#NUM!)を返します)
EXCELの期間計算においてここまで厳密に行う必要があるケースはまれかと思います。(クリティカルな業務ならEXCELなど使わないでしょうから:-P
なお動作確認用のサンプルを掲載しましたのでご利用ください。

関連情報:
No.50 DATEDIFの謎を追え(1) 〜 DATEDIF関数の真実
No.52 DATEDIFの謎を追え(3) 〜 DATEDIF関数の実装(ノーツ編)

使用条件・免責事項



[戻る]