エクセルで請求書の支払期限や在庫の経過日数を管理していると、日付同士を引き算して日数の差を求めたい場面が頻繁に出てきます。
また勤怠管理や工程管理では、単純な日数だけでなく土日祝日を除いた営業日数を求めたいというケースも多いでしょう。
しかし日付を単純に引き算しただけでは、思った通りの数値が表示されずに戸惑ってしまう方も少なくありません。
この記事では【Excel】エクセルで日付の引き算をする（日数の差・DATEDIF・営業日計算・NETWORKDAYS）方法について、基本の計算から関数を使った応用まで詳しく解説していきます。
ポイントは
・単純な引き算で日数の差を求める方法
・DATEDIF関数で年数や月数まで正確に計算する方法
・NETWORKDAYSやNETWORKDAYS.INTL関数で土日祝日を除いた営業日数を求める方法
で、使用する主な数式は
=終了日のセル-開始日のセル
=DATEDIF(開始日のセル,終了日のセル,”D”)
=NETWORKDAYS(開始日のセル,終了日のセル,祝日リストの範囲)
=NETWORKDAYS.INTL(開始日のセル,終了日のセル,週末番号,祝日リストの範囲)
です。
それでは詳しく見ていきましょう。
エクセルで日付の引き算をして日数の差を求める方法（基本の引き算）
エクセルの内部では、日付はシリアル値と呼ばれる連続した数値として管理されています。
そのため終了日のセルから開始日のセルを引き算するだけで、その間の日数の差をそのまま求めることができます。
具体的には、日数を表示したいセルに終了日のセルと開始日のセルを指定し、その差を計算する数式を入力するだけです。
下のイメージ図のように、C2セルに数式を入力してからフィルハンドルをドラッグすれば、残りの行にも同じ考え方の数式が自動的に適用されます。
ただし注意したいのは、引き算の結果を入力したセルがもともと日付の書式になっている場合、答えも日付として表示されてしまう点です。
たとえば結果が1901年1月13日のような表示になってしまったら、それは日数の計算自体は正しいものの表示形式が日付のままになっているサインです。
この場合はホームタブの数値グループから表示形式を標準または数値に変更すれば、正しい日数の数字が表示されます。
また開始日と終了日を逆に指定してしまうと、結果がマイナスの数値になってしまうので、どちらが先の日付かを必ず確認しておきましょう。
【日付同士は終了日から開始日を引くだけで日数の差がそのまま数値として求められる、結果が日付表示になってしまった場合はセルの表示形式を標準に変更する】
DATEDIF関数で年数や月数まで正確に計算する方法
単純な引き算だけでは、たとえば入社から何年何ヶ月経過したかといった細かい単位の計算が難しくなります。
そこで役立つのがDATEDIF関数で、開始日と終了日の間の期間を年数や月数、日数などさまざまな単位で取り出すことができます。
DATEDIF関数の書式は、DATEDIF開始日のセル、終了日のセル、単位という形で指定します。
この単位の部分に指定できる文字列にはいくつかの種類があり、それぞれ計算される内容が異なります。
DATEDIFの単位一覧と使い方
単位にYを指定すると、開始日から終了日までの満年数が求められます。
単位にMを指定すると、満月数が求められるので、勤続年数を月単位で確認したいときに便利です。
単位にDを指定すると、単純な引き算と同じく開始日から終了日までの日数がそのまま求められます。
単位にYMを指定すると、年数を無視した1年未満の端数の月数だけを取り出すことができます。
単位にMDを指定すると、月数を無視した1ヶ月未満の端数の日数だけを取り出すことができます。
単位にYDを指定すると、年数を無視した1年未満の端数の日数だけを取り出すことができます。
これらを組み合わせれば、生年月日から満何歳何ヶ月何日というような表現も作ることができます。
なおDATEDIF関数は、数式の入力時に候補として表示されない、いわば隠し関数のような扱いになっています。
そのため関数を選ぶ画面から探しても見つからず、セルに直接手入力する必要がある点は覚えておきましょう。
DATEDIFのMD引数を使う際の注意点
DATEDIF関数はとても便利な反面、単位にMDを指定した場合に限って、日付の組み合わせによっては正しい結果が返らないことがあります。
これは古くから知られているエクセル側の仕様であり、月末付近の日付を扱う際には結果がずれる可能性があるためです。
正確な端数日数がどうしても必要な場合は、YM単位で月数を求めたうえで、終了日からその月数分を差し引いた日付との差を別途計算するといった工夫をすると安心です。
【DATEDIFは単位をYMDYMMDYDと変えることで年数や月数、端数の日数まで柔軟に取り出せるが、関数の候補には表示されないため直接入力する必要がある】
NETWORKDAYS関数で土日を除いた営業日数を計算する方法
単純な日数の差やDATEDIF関数は、あくまで暦の上での日数を求めるものなので、土日祝日が含まれていてもそのまま数えてしまいます。
しかし勤怠管理や納期計算では、土日を除いた実際の営業日数が知りたいことのほうが多いでしょう。
このようなときに使えるのがNETWORKDAYS関数で、開始日と終了日の間にある土曜日と日曜日を自動的に除外して日数を数えてくれます。
書式はNETWORKDAYS開始日のセル、終了日のセル、祝日リストの範囲という形で指定します。
祝日リストの部分は省略することもでき、その場合は土日だけを除いた日数が計算されます。
祝日リストを除外する方法
土日に加えて祝日も除外したい場合は、あらかじめ別のセル範囲に祝日の日付を一覧として入力しておきます。
そのうえで、NETWORKDAYS関数の第三引数にその祝日リストの範囲を指定すれば、土日と祝日の両方を除いた正味の営業日数が求められます。
このとき祝日リストの範囲は、下方向にオートフィルしても参照範囲がずれないよう、絶対参照にしておくことが重要です。
なお祝日リストに登録した日付がもし土日と重なっていた場合でも、二重にカウントされることはなく、正しく1日分だけ除外されます。
【NETWORKDAYSは土曜日と日曜日を自動的に除外し、祝日リストの範囲を絶対参照で指定すれば正確な営業日数が求められる】
NETWORKDAYS.INTL関数で休日パターンを柔軟に設定する方法
NETWORKDAYS関数は土曜日と日曜日を休日とする前提で計算されるため、シフト制の職場などで休日が固定されていない場合には対応しきれません。
そこで使えるのがNETWORKDAYS.INTL関数で、どの曜日を休日として扱うかを番号で自由に指定できます。
書式はNETWORKDAYS.INTL開始日のセル、終了日のセル、週末番号、祝日リストの範囲という形になります。
週末番号には1から7までの数字を使う方式と、11から17までの数字を使う方式があります。
たとえば週末番号に1を指定すると土曜日と日曜日が休日になり、週末番号に2を指定すると日曜日と月曜日が休日として扱われます。
一方で11から17の番号を使うと、休みたい曜日をひとつだけに絞ることができ、たとえば11を指定すると日曜日だけが休日として扱われます。
このように柔軟に休日の曜日を設定できるため、飲食店や医療機関のように土日が営業日で平日に定休日があるような業種でも正しく営業日数を求められます。
祝日も同時に除外したい場合は、NETWORKDAYS関数と同様に第四引数へ祝日リストの範囲を絶対参照で指定すれば対応できます。
【NETWORKDAYS.INTLは週末番号を指定することで土日以外の曜日や特定の一曜日だけを休日として営業日数を計算できる】
日付の引き算をする際によくあるエラーと注意点
| 症状 | 主な原因 | 対処法 |
|---|---|---|
| 結果が日付表示になる | セルの書式が日付のまま | 表示形式を標準に変更 |
| 結果がマイナスになる | 開始日と終了日が逆 | 引き算の順番を入れ替える |
| 結果がVALUEエラーになる | 日付が文字列として入力されている | 日付データとして入力し直す |
日付の引き算は基本的にシンプルな操作ですが、いくつか陥りやすい落とし穴があります。
あらかじめ代表的な症状と原因を知っておくと、実際にエラーが起きたときにも慌てず対処できるでしょう。
結果が日付表示になってしまう場合
日付から日付を引き算した結果を表示するセルが、あらかじめ日付の書式に設定されていると、答えの数値までもが日付として解釈されてしまいます。
この場合はセルを選択し、ホームタブの数値グループにあるプルダウンから標準または数値を選び直すことで解決します。
右クリックからセルの書式設定を開き、分類の一覧で標準を選ぶ方法でも同じ結果になります。
日数がマイナスになってしまう場合
引き算の結果がマイナスの数値で表示された場合、多くは開始日と終了日の指定を逆にしてしまっていることが原因です。
数式を終了日から開始日を引く形に修正すれば、正しいプラスの日数が表示されるようになります。
また日付が文字列として入力されている場合は、セルの左側に寄って表示されるため見分けがつきやすく、この場合は日付データとして入力し直す必要があります。
あわせて列の幅が狭いことでシャープの記号が並んで表示されることがありますが、これは単に列幅が不足しているだけなので、列の境界をダブルクリックして幅を自動調整すれば解消します。
【引き算の結果が日付表示になる場合はセルの表示形式を標準に変更し、マイナスになる場合は開始日と終了日の順番を必ず確認する】
まとめ エクセルで日付の引き算をする方法（NETWORKDAYS・営業日計算・DATEDIF・日数の差）
エクセルで日付の引き算をする方法をまとめると、終了日から開始日を引くだけの基本的な計算から、DATEDIF関数を使った年数や月数の算出、そしてNETWORKDAYSやNETWORKDAYS.INTL関数を使った営業日数の計算まで、目的に応じていくつかの方法を使い分けることが大切です。
単純に暦の上での日数を知りたいだけであれば引き算だけで十分ですが、勤続年数や年齢のように年月日の単位で表現したい場合はDATEDIF関数が役立ちます。
さらに土日祝日を除いた実質的な営業日数を求めたい場合は、NETWORKDAYS関数や、休日の曜日を柔軟に設定できるNETWORKDAYS.INTL関数を活用しましょう。
結果が日付表示になってしまう場合はセルの表示形式を標準に変更し、開始日と終了日の前後関係を確認するといった基本的な注意点を押さえておけば、ほとんどのケースで思い通りの日数計算ができるようになります。
業務内容に合わせてこれらの方法を組み合わせながら、エクセルでの日付計算を効率よく活用していきましょう。
コメント