目次
EXCELで日付の差を計算するDATEDIF関数の落とし穴と回避方法
EXCELのDATEDIF関数についてまとめます。
DATEDIFは非公式関数
DATEDIF関数は、日付の差を月単位・年単位で計算する時に便利なのです。
Office365のヘルプでも、日付の差を計算にはDATEDIF関数を使ったやり方が紹介されているのに、EXCELの標準に含まれない「非公式関数」だったりします。
標準ではないから、関数の挿入ダイアログで検索してもでてきません。
DATEDIFは間違った計算結果を返すこともある
マイクロソフトのヘルプを見ると、さらに驚くべきことが書いてあります。
引用すると。
Excel では、Lotus 1-2-3 の古いブックをサポートするために、DATEDIF 関数が用意されています。
DATEDIF 関数では、特定のシナリオで誤った計算結果を返すことがあります。
DATEDIFは非公式であるだけではなく、「誤った計算結果を返すことがある」などと堂々と宣言されながらも、ずっと修正されずに放置されている特殊な位置づけの関数なのです。
DATEDIFは落とし穴を知って使うべき関数
DATEDIF関数ができることは、２つの日付の差を求めることだけです。
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")」みたいに、日付をリテラルで書いてます。
これはブログに書く都合上仕方なしにやってます。
実際に使う時は、開始日、終了日のセルを作って、セル参照で式を書いてください。
落とし穴その１：うるう年を処理できない"MD"
”MD"、”YD"は、「うるう年を処理できない」バグがあると書いている部分です。
”MD"は、うるう年がはさまると、かなりの確率でOUTになります。
例えば、直近のうるう年の2016年のケースでやってみます。
以下の関数をEXCELのセルにコピペしてみてください。
=DATEDIF("2016/1/31","2016/3/1","MD")
上記の式の正解は「1」です。
最後の3月にはいってからの端数は1日だけなのは、暗算でもできますよね。
でも、悲しいかなEXCELは「-1」と表示するはずです。
これはDATEDIFを使った回避方法はありません。
どうしても・・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)))))
自分で試した限りでは、何となくあっているっぽいですが・・。
使うなら、自己責任でどうぞ。
一番いいのは「1月内の端数の日数を計算」をあきらめることです（笑）
落とし穴その２：うるう年を処理できない"YD"
同じ理由で”YD”もやばいです。
同じく2016年のケースでいきます。
以下の関数をEXCELのセルにコピペしてみてください。
=DATEDIF("2015/1/1","2016/2/29","YD")
=DATEDIF("2015/1/1","2016/3/1","YD")
上記の２つの式は同じ答えを返すはずです。
当然、終了日が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)
これも、自分で試した限りでは、答えはあっているようでした・・けど、使うなら、自己責任でお願いします。
これも、一番いいのは「1年内の端数日数を計算」をあきらめることです（笑）
落とし穴その3：月末日の処理が甘い"M" と"YM"
”Y"、”M"、”YM"です。
仕様の落とし穴が隠れているので、注意して使う必要があります。
”YM”と”M"は同じ問題を持っています。
不具合ではなくて、独特の仕様ということみたいです。
”YM"と”M"で落とし穴にはまるパターンは、以下の２つの条件がそろった時です。
- 開始日も終了日も月の末日である
- 開始日の日＞終了日の日の関係がなりたつ
例をあげます。
- 開始日 11月30日、終了日 12月31日
- 開始日 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の仕様・・らしいのですが、月末日をちゃんと認識してくれないのです。
これを回避する方法はあります。
終了日に「＋１」して使うだけです。
こんな感じで。
=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))
これも、自分で検算する限りは正しい答えを返しているみたいですが、こんな関数を使ったシートなんて、誰もメンテナンスできなくなるので、実用性はありません。
終了日にプラス１する技を使うことをお勧めします。
落とし穴その4：うるう年の処理に問題がある"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"と同様に、終了日に＋１日して計算するやり方です。
この具体例は、最後の方で年齢計算の例として説明しています。
落とし穴その5：暗黙の時刻「0時0分0秒」
これもEXCELの仕様の問題です。
1日は24時間あるので、仮に「2019/07/01」みたいに日付だけを指定しても、その日の何時何分何秒時点をとらえるか・・で計算結果が変わります。
EXCELの場合、日付のシリアルの基準は「0」・・つまり「0時0分0秒」です。
それを文章でそれっぽく書くと
日付だけを指定した場合に隠れている暗黙の時刻は”0時0分0秒”である
みたいになるわけです。
これによって、1日から末日で1月と数えるというルールにした場合に、ちょっと困ったことがおきます。
人間的には「末日」で1ケ月経過したとカウントするのは、ごく自然なので、下記の図のように1月1日から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日が終わっていないので、１カ月とカウントされず、7月末までの「7」になる。
仕様通りの動作です・・けど、人間から見るとなんかモヤモヤ・・します。
正しく、8月31日終了時点までの月数を計算したいときはどうするか？
これも以下のように終了日に「＋１」してやれば良いわけです、
=DATEDIF("2019/1/1","2019/8/31"+1,"M")
こうすると、終了日は「9月1日の0時0分0秒」になりますから、8月末までがちゃんと計算されて、答えは「８ヵ月」になります。
ちなみに。
これはDATEDIFだけの話ではありません。
人間なら、1月1日から1月31日は何日間か？と聞かれれば「31日」と、ほとんどが答えるはずです。
でも、EXCELで以下のように計算すると
="2019/1/31"-"2019/1/1"
「30」日間と計算結果が返ってきます。
EXCELの仕様から言えば「正しい計算結果」なんですけど、人間は1月31日まで・・と聞くと暗黙的に31日の終了時点を思い浮かべてしまうので、違和感を感じる人は多いみたいで、実際の話、これで悩んでた人を、自分はいっぱい知ってます（笑）。
DATEDIFを安全に使う方法
DATEDIF関数は「”Y",”YM","M"」の３つだけ使いましょう。
そして、使うときには必ず「終了日に＋１して使う」やり方をするようにしましょう。
これがDATEDIFを正しく使う方法です。
例として、最後に「法律に定められたルールに従った年齢計算」をしてみます。
一般的に年齢計算というと、誕生日が来て1つ歳を取る計算方法が「慣習的」に使われていますけど、法律的には「前日で１つ歳をとる」のが正しい年齢の数え方です。
こちらの記事で理由を解説してくれてます。
当たり前ですが、年金とか社会保険とか、年齢要件で「○○歳から・・」って書いてある場合は、全部、前日で１つ歳をとる法律で定められた年齢計算でないと駄目です。
実は、法律通りの年齢計算には、うるう年の2月29日生まれの人の場合だけ特別なルールがあります。
上記サイトを引用すると。
年齢は、誕生日の前日に、１歳年をとると計算する。
ただし、２月２９日生まれの人だけは、閏年でも平年でも２月２８日に１歳年をとると計算する
とややこしい仕様が書いてあります。
でも、DATEDIFの”Y"を使って、終了日に「＋１」すれば、前日で１つ歳をとる・・という要件が簡単に実現できるだけでなく、上記のうるう年の例外条件までカバーできる「完璧な年齢計算」ができるのです。
例えば。
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関数について書きました。
この記事がお役に立てれば、いいのですけどね。
ではでは。
