"BOKU"のITな日常

還暦越えの文系システムエンジニアの”BOKU”は新しいことが大好きです。

EXCELで日付の差を計算するDATEDIF関数の落とし穴を整理します。

いまさらなのですが、EXCELのDATEDIF関数について書いてみます。

DATEDIFは、簡単に使えて便利なのですが、落とし穴も色々ある癖のある関数なので、その辺を整理しておこうと思います。

f:id:arakan_no_boku:20190702234539j:plain

 

そもそもEXCELの「非公式関数」です

 

EXCELで日付の差を月単位・年単位で計算する機会は多くあります。

そんな時の選択肢がDATEDIF関数です。

他の方法もないわけではありませんが、ややこしいので、Office365のヘルプでも、日付の差を計算にはDATEDIF関数を使ったやり方が紹介されています。

support.office.com

なのに。

関数の挿入ダイアログで検索してもでてきません。

f:id:arakan_no_boku:20190702235622p:plain

理由は簡単。

EXCELの標準に含まれない「非公式関数」だからです。

マイクロソフトのヘルプにも、さりげなく明記されています。

support.office.com

引用すると。

Excel では、Lotus 1-2-3 の古いブックをサポートするために、DATEDIF 関数が用意されています。

DATEDIF 関数では、特定のシナリオで誤った計算結果を返すことがあります。

なんと、DATEDIFは非公式であるだけではなく、「誤った計算結果を返すことがある」などと堂々と宣言されながらも、ずっと修正されずに放置されているという、ちょっと特殊な位置づけの関数なのです。

そりゃあ、「落とし穴のひとつやふたつ」は、あって当然・・でしょうね。

 

DATEDIF関数の概要

 

DATEDIFができることは、2つの日付の差を求めることだけです。

DATEDIF(開始日,終了日,単位)

という書式で、単位のところに以下の6種類が指定できます。

  • "Y" : 期間の年数です。
  • "M" : 期間の月数です。
  • "D" : 期間の日数です。
  • "MD" : 年月を無視して1月内の端数の日数を計算します。
  • "YM" : 年日を無視して、1年の中での端数の月数を計算します。
  • "YD" : 年月を無視して1年の中での端数の日数を計算します。

とはいえ。

”D"は、日付同士の引き算をするのと違わないので、あえて使う意味がないですし、”Y"、”M"、”YM"には、仕様の落とし穴が隠れていて、それを認識して使わないと、たまに間違った計算結果で困ることがあります(経験談です・・笑)。

”MD"、”YD"については、「うるう年を処理できない」バグがあって、なかなか回避するのが難しいので、使わないのが無難です。

こう書くと、落とし穴を気にせず使える機能は、ほぼ・・ないですよね。

以下、そのへんを個別に補足していきます。

なお。

以後の関数の使用例では「=DATEDIF("2016/1/31","2016/3/1","MD")」みたいに、日付をリテラルで書いてます。

これはブログに書く都合上仕方なしにやってます。

実際に使う時は、開始日、終了日のセルを作って、セル参照で式を書いてください。

f:id:arakan_no_boku:20190704001957p:plain

いわずもがな・・ですけど。

 

DATEDIFの落とし穴その1:バグ

 

まずは、上記で「”MD"、”YD"については、「うるう年を処理できない」バグがあって、なかなか回避するのが難しいので、使わないのが無難です。」と書いている部分です。

 

使わない方が良い”MD"

 

まず”MD"です。

これは、うるう年がはさまると、かなりの確率でOUTになります。

例えば、直近のうるう年の2016年のケースでやってみます。

以下の関数をEXCELのセルにコピペしてみてください。

=DATEDIF("2016/1/31","2016/3/1","MD")

上記の式の正解は「1」です。

最後の3月にはいってからの端数は1日だけなのは、暗算でもできますよね。

でも、悲しいかなEXCELは「-1」と表示するはずです。

マイナスの数字を返されてしまうと話にならないので、使わない方が無難です。

もちろん、DATEDIFを使わないで標準関数だけでやる方法はあります。

無理やりやってみると・・例えば、K20に開始日、L20に終了日がはいっているという想定で以下の式みたいになるのかな?

=IF(DAY(L20+1)=1,MOD(MAX(DAY(L20)-DAY(K20+1)+1,0),DAY(L20)),
IF(DAY(K20+1)=1,DAY(L20),MOD(DAY(L20)-DAY(MIN(DATE(YEAR(L20),
MONTH(L20)-{1,0},DAY(K20)*{1,0}))+1)+1,DAY(L20-DAY(L20)))))

まあ、自分で試した限りでは、何となくあっているっぽいですけど、バグがあるかどうかを見極めるのは、相当困難な作業になるという雰囲気はわかると思います。 

 

同じく使わない方が良い”YD"

 

YDもやばいです。

同じく2016年のケースでいきます。

以下の関数をEXCELのセルにコピペしてみてください。

=DATEDIF("2015/1/1","2016/2/29","YD")

=DATEDIF("2015/1/1","2016/3/1","YD")

上記の2つの式は同じ答えを返すはずです。

当然、終了日が3月1日の方の計算結果は間違いです。

しかも、それだけではなくて、3/1の時点で発生した1日のずれは、以後ずーっとそのままです。

つまり、うるう年の3月1日以降は、その年中ずっと1日間違った答えになります。

これも完璧にOUTですよね。

うるう年以外なら大丈夫ですが、まあ、使わない方が無難です。

これもDATEDIFを使わず、標準関数でやる方法はあります。

K30に開始日、L30に終了日がはいっている想定で以下の式とか。

=MOD(L30-K30,MAX(INT((YEAR(L30)*12+MONTH(L30)-YEAR(K30)*12-MONTH(K30)
-IF(DAY(K30+1)=1,IF(DAY(L30+1)>1,1),IF(AND(DAY(L30+1)>1,
DAY(L30)<DAY(K30)),1)))/12),1)*365)

これも、自分で試した限りでは、答えはあっているようでしたが、DATEDIFを使わないと「こんな複雑になるよ」って例なので、あまり、突っ込まないでくださいね(笑) 

 

DATEDIFの落とし穴その2:月末日の処理が甘い「仕様」

 

今度は「”Y"、”M"、”YM"には、仕様の落とし穴が隠れていて、それを認識して使わないと、たまに間違った計算結果で困ることがあります(経験談です・・笑)」と書いている部分についてです。

 

 注意して使うべき”YM"と”M"

 

”YM”と”M"は同じ問題を持っています。

不具合ではなくて、独特の仕様ということみたいですが。

”YM"と”M"で落とし穴にはまるパターンは、例えば、以下の2つの条件がそろった時です。

  • 開始日も終了日も月の末日である
  • 開始日の日>終了日の日の関係がなりたつ

例をあげます。

  1. 開始日 11月30日、終了日 12月31日
  2. 開始日 3月31日 終了日 4月30日

これはどちらも月末日から翌月末日なので、人間ならだれでも、月数で言えば「1」月を計算結果として期待するはずです。

でも、以下の関数をEXCELのセルにコピペして実行してみてください。

=DATEDIF("2016/11/30","2016/12/31","M")

=DATEDIF("2016/3/31","2016/4/30","M")

=DATEDIF("2016/11/30","2016/12/31","YM")

=DATEDIF("2016/3/31","2016/4/30","YM")

3/31を開始日に来た方は、「0」月を返すはずです。

EXCELの仕様・・らしいのですが、月末日をちゃんと認識してくれないのです。

これを回避する方法はあります。

終了日に「+1」して使うだけです。

こんな感じで。

=DATEDIF("2016/11/30","2016/12/31"+1,"M")

=DATEDIF("2016/3/31","2016/4/30"+1,"M")

=DATEDIF("2016/11/30","2016/12/31"+1,"YM")

=DATEDIF("2016/3/31","2016/4/30"+1,"YM")

これで、どの式も問題なく「1」月を返してくれます。 

もちろん。

これもDATEDIFを使わないで、EXCELの標準関数でやる方法はあります。

”M"の場合なら、L50に終了日、K50に開始日を入力して、こんな計算式を描くとか・・ですね。

=YEAR(L50)*12+MONTH(L50)-YEAR(K50)*12-MONTH(K50)
-IF(DAY(K50+1)=1,IF(DAY(L50+1)>1,1),IF(AND(DAY(L50+1)>1,
DAY(L50)<DAY(K50)),1)) 

これも、自分で検算する限りは正しい答えを返しているみたいですが、正直、メンテナンスする気にはなりません。

終了日にプラス1する技を使った方がマシです。

 

ピンポイントで間違うリスクがある”Y"

 

”Y"で問題があるのも「うるう年」です。

それもピンポイントで、うるう年の2月29日開始で1年を判断する場合だけです。

これも不具合ではなくて仕様らしいです。

つまり。

2月29日から1年後の日のことをとらえる考え方らしく。

  • 2/29の1年後をうるう年でない時の3/1ととらえる
  • 2/29は2月末ということで、うるう年でないときは2/28で1年ととらえる

と2通りあるうちで、DATEDIFの”Y"は上の方を採用している・・らしいです。

これも、「月末日の認識をちゃんとしてくれない」ゆえの問題です、 

悲しいかな。

仕様だと言われても、日本人なら下の方(2月末でとらえる)の感覚が普通だと思うので、とても違和感のある・・結果になります。

これも、回避方法はあります。

”YM",”M"と同様に、終了日に+1日して計算するやり方です。

この具体例は、最後の方で年齢計算の例として説明しています。

 

DATEDIFの落とし穴その3:暗黙の時刻「0時0分0秒」

 

これもEXCELの仕様の問題です。

不具合ではなく、個別に見ると正しい仕様だと思えるのですが、つい、感覚的に間違いを生む原因になりやすい、まさに「落とし穴」です。

 

暗黙の時刻とは

 

簡単な話。

1日は24時間あるので、仮に「2019/07/01」みたいに日付だけを指定しても、その日の何時何分何秒時点をとらえるか・・で計算結果が変わります。

なので、日付だけが指定された場合は時刻の部分は「基準」でそろえておかないとだめなのは当然で、EXCELの場合、日付のシリアルの基準は「0」・・つまり「0時0分0秒」ですよ・・ということです。

www.officepro.jp

それを文章でそれっぽく書くと

日付だけを指定した場合に隠れている暗黙の時刻は”0時0分0秒”である

みたいになるわけです。

これだけでは、わかりづらいので、例で説明します。

例えば。

以下のようなケース。

f:id:arakan_no_boku:20190703213345p:plain

ルールとして、1日から末日で1月と数えましょうというパターンです。

上記のように図に書くと、1月1日~8月31日までを月数で表すと「8ヵ月」になるというのは、直感的にわかります。

なので、つい以下のように関数書いてしまったりします。

=DATEDIF("2019/1/1","2019/8/31","M")

一見、正しそうです。

でも、計算結果には「7」が返ってくるはずです。

当たり前なのですけどね。

日付だけの時、暗黙的に隠れている時刻は「0時0分0秒」なのですから。

時刻を補完して「2019/1/1 0時0分0秒」   から 「2019/8/31 0時0分0秒」と書くと、すぐわかります。

8月31日の0時0分0秒は、まだ31日の開始時点でしかないので、計算結果の月数は「8月30日終了時点」までになります。

だから、8月は月末日の31日が終わっていないので、1カ月とカウントされず、7月末までの「7」であるのが当然です。

じゃあ。

8月31日終了時点までの月数を計算したいときはどうするか?

以下のように終了日に「+1」してやれば良いわけです、

=DATEDIF("2019/1/1","2019/8/31"+1,"M")

こうすると、終了日は「9月1日の0時0分0秒」になりますから、8月末までがちゃんと計算されて、答えは「8ヵ月」になります。

ちなみに。

これはDATEDIFだけの話ではありません。

人間なら、1月1日から1月31日は何日間か?と聞かれれば「31日」と、ほとんどが答えるはずです。

でも、EXCELで以下のように計算すると

="2019/1/31"-"2019/1/1"

「30」日間と計算結果が返ってきます。

EXCELの仕様から言えば「正しい計算結果」なんですけど、人間は1月31日まで・・と聞くと暗黙的に31日の終了時点を思い浮かべてしまうので、違和感を感じる人は多いみたいで、実際の話、これで悩んでた人を、自分はいっぱい知ってます(笑)。

 

終了日に「+1」はかなり有効です

 

”Y",”YM","M"の場合に「終了日に+1して使う」というやり方は、人間の感覚と計算結果のズレをなくすには、結構有効です。

とはいえ。

実際の例がないと、ピンとこないでしょうから、「法律に定められたルールに従った年齢計算」を例にしてみます。

一般的に年齢計算というと、誕生日が来て1つ歳を取る計算方法が「慣習的」に使われています。

まあ、日常生活で使う分には問題ないですしね。

でも、厳密に言うと、その計算方法は間違いです。

法律的には「前日で1つ歳をとる」のが正しい年齢の数え方です。

こちらの記事で理由を解説してくれてます。

jinjisan.hatenablog.com

当たり前ですが、年金とか社会保険とか、年齢要件で「○○歳から・・」って書いてある場合は、全部、前日で1つ歳をとる法律で定められた年齢計算でないと駄目です。

実は、法律通りの年齢計算には、うるう年の2月29日生まれの人の場合だけ特別なルールがあります。

上記サイトを引用すると。

年齢は、誕生日の前日に、1歳年をとると計算する。

ただし、2月29日生まれの人だけは、閏年でも平年でも2月28日に1歳年をとると計算する

なのです。

ややこしいですね。

でも、DATEDIFの”Y"を使って、終了日に「+1」すれば、前日で1つ歳をとる・・という要件が簡単に実現できるだけでなく、DATEDIFでそれをすることで、上記のうるう年の例外条件までカバーできてしまったりします。

論より証拠でやってみます。

例えば。

2016年2月29日生まれの人です。

ルールに従えば、うるう年でない2019年も、うるう年である2020年も28日で1つ歳をとらないといけません。

2019/2/28に3歳、2020/2/28に4歳になるわけですね。

DATEDIFで書くと、以下のような感じ。 

=DATEDIF("2016/2/29","2020/2/28"+1,"Y")

=DATEDIF("2016/2/29","2019/2/28"+1,"Y")

コピペしてみてください。

上は「4」歳、下は「3」歳とちゃんと返ってくるはずです。

そして、両方とも2/27日には歳をとりません。

=DATEDIF("2016/2/29","2020/2/27"+1,"Y")

=DATEDIF("2016/2/29","2019/2/27"+1,"Y")

これをコピペすると、上は「3」歳、下は「2」歳のままになるはずです。

結構、いい感じですよね。

 

まとめ

 

 EXCELのDATEDIFは非公式関数でバグもありますし、落とし穴もあります。

でも、上記の年齢計算みたいに、その辺をわかって使う分には、簡単に使えて便利な関数であるのは間違いないです。

なので、十分に注意して使いましょう。

結論はそれにつきます(笑)

この記事がお役に立てれば、いいのですけどね。

ではでは。