こんにちは。ヒトツメです。
今日は、Excelにおける日付の考え方と、それに関連する日付系の関数について解説をしていきたいと思います。
「1」=「1900年1月1日」
まずはじめに、Excelでの日付の考え方について解説していきますが、結論から言うと、日付型のデータに関して、特定のデータを持っているわけではありません。日付一つ一つに、全て数字が割り振られています。
例えば、「=TODAY()」と今日を示す関数を入れ、その関数を入れたセルの書式を数値に変換すると、次のように、45,000前後くらいの数字が出ます（下記の画像を取得したのは、2022年7月9日です）。
これは、1900年1月1日を「1」として、対象の日付が何日目に該当するか、という形でデータが作成されていることによるものです。試しに、「1」といれたセルと「1900年1月1日」と入れたセルをイコールでつなぐと、下記のようにTRUEが返却されます。
ちなみに、時刻も同じように処理しているので、「1.5」とセルに入れて形式を時刻にすると、1900年1月1日のちょうど真ん中、その日の12時が表示されます。
年、月、日、時、分、秒
しかしながら、人間が生活している中で、全てを「何日後」という形で整理することはできません。うるう年かどうかで、1年後の意味は、365日後にもなりますし、366日後にもなります。同じように、何か月後、といっても、それが2月の話なのか3月の話なのかで、何日後かは変わってしまいます。
そこで、日付だけでなく、年・月や、時・分・秒・で処理するための関数が必要になります。
基本の考えは要素を取り出す
基本的には、こういった問題を解決するためには、日付や時刻のデータに含まれる、年・月・日・時・分・秒を取り出すということです。
それぞれ、次のような関数でそれぞれの要素を取り出すことが出来ます。
- 年：YEAR関数
- 月：MONTH関数
- 日：DAY関数
- 時：HOUR関数
- 分：MINUTE関数
- 秒：SECOND関数
上記のように、日付を要素に分解することで、二つの間の日付が正確に、「何年何か月と何日後」か計算することが出来ます。
ちなみに、4行目は単に3行目と2行目の数字を比較していますが、そこにマイナスが発生すると上記のような形式で表せなくなるため、5行目の年と月は、「=IF(C4<0,B4-1,B4)」として、マイナスだった場合に一つ数字を引くようにしています。日付の部分だけ、マイナスになった場合には、特殊な計算を入れることで、うまく日付を出せるようにしています（なお、上記では、月の部分と日の部分両方がマイナスの場合はうまく動かないので、もう少し工夫が必要です。ぜひどうすればうまく出るか考えてみてください。）。
DATEDIF関数
とはいえ、毎回調整の計算を入れるのは面倒です。そこで日付に関して、Excelでは、DATEDIF関数という関数が用意されています。
これは、第1引数に開始日、第2引数に終了日、第3引数に単位を示す英文字を入れることで、簡単に上記のような比較ができるようになっています。
例えば第3引数に「”Y”」を入れると、二うの日付を比較し、満何年経っているかを計算することができます。おなじように、「”M”」を入れると満何か月か、「”D”」を入れると満何日経っているかを計算することができます。
また、引数に「”MD”」を入れると、日付の月数および年数を無視して日付を計算することも可能ですが、うまく動かないケースがあることが分かっており、Microsoftはこの使い方を推奨していません。
営業日数を計算することも可能
また、NETWORKDAYS関数を使うと、二つの日付の営業日数の差を求めることもできます。国によって祝日が異なるため、祝日リストを使うなど、工夫が必要ですが、「何営業日後」という計算をすることは、仕事でも多いと思いますので、意外と使う場面は多いです。
こちらは次回以降に詳しく解説していこうと思います。
〇か月後はEDATE関数で！
今度は逆に、何年後、何か月後、という日付をする場合を考えていきます。
この場合も基本的な考え方は同じで、要素に分解して、要素ごとに足しこんで、日付型に戻すという考え方を採ります。
DATE関数を使えば、例えば「=DATE(2022,7,9)」と入れると、2022年7月9日が返却されますので、この要素一つ一つに、分解して足しこんだ数字を入れてあげると、希望の日付が返却されます。
この関数が便利なのは、ある程度の範囲は決まっているものの、月に12以上の数字を入れたり、日に31以上の数字を入れても、うまく作動する点です。
〇か月後が年をまたぐ場合でも、計算できるので、非常に便利です。
※余談ですが、例えば「=DATE(2022,5,0)」と入れると、その前の月の末日、つまり2022年4月30日が返却されます。意外と便利なので覚えておくといいと思います。
もっとも、ただ「〇か月後」というのを出すだけであれば、EDATE関数の方が便利です。例えば「=EDATE(TODAY(),4)」と入れると、丁度今日から数えて4か月後の日付が返却されます。引数も少ないですし、非常に便利です。
さいごに
日付に関しては、書ききれないほど、Excelでは本当に色々な関数が良いされています。
一つ一つをしらみつぶしに覚えるのは大変ですが、やりたいことの要素を分解して、中間式なども用いながら結論にたどり着けるようになると、非常に業務が効率化できます。
基本的な考えは、年・月・日などの要素に分解して処理するということなので、まずはそこから覚えていただくと良いと思います。
コメント