【Excel】「納期まであと何日？」日数、月数、営業日数などの期間を計算する：Tech TIPS
ビジネスの現場では、納期や締め切り日などから作業を逆算しなければならないことも多い。このような場合、平日を手で数えるのは効率が悪い。Excelを使えば、簡単に平日の日数などが計算可能だ。
対象：Office 2013／2016／2019／365
仕事では、作業時間が問題になることがある。特に納期や締め切り日など、日時に関係して、作業時間を設定したり、逆に必要な作業時間と締め切りから開始予定日を逆算したりすることがある。何げなく扱っているが、「時刻」と「時間」は、似ているようで違うものだし、締め切りまでの期間がどれだけあるのかが問題になることも多い。
「期間」とは
「期間」といったとき、特定の日時から始まり、特定の日時に終わる日時までの間を指す。「1週間」や「3カ月」といったように表される。特にビジネスの現場では、「終了日時」が問題になることが多く、終了日時から期間を使って逆算して、開始日時を決めたり、逆に終了日時と現在の日時から期間を算出したりすることがある。期間には、必ず何らかの日付時刻が関係する。
また、「期間」では、「年」や「月」「日」といった単位にとらわれることなく「36時間」「280日」「18カ月」といった表記をすることもある。これは、作業などにより、必要となる「精度」が異なるからだ。
作業の完了に対して、日付だけでなく時刻まで指定されているような場合で、時間単位で期間を表記することで、作業時間を明確にできる。「今週水曜日の12時まで」といったスケジュールが決まっているとき、それまでの作業期間は時間単位で考える必要がある。月曜日の12時からならば、48時間といった具合だ。
これに対して、荷物の配達など営業時間内のどこかで行われる作業は「今週の水曜日」に対して「3営業日」など日にち単位で扱うだろう。製造業などでは、製品企画段階では、工場の製造スケジュールや設計期間など未定な部分が多く、あまり細かく期間を考えてもどうしようもないことがあるため、ざっくりと月単位での期間を考えることがあるだろう。
つまり、期間を処理する場合、「開始日」「終了日」「期間」「期間の精度」の4つの要素を考慮する必要がある。
時刻と時刻の間隔を「時間」というが、時刻と時刻の間は24時間以上離れることもあるものの、単純に「1時間」などの短い時間単位などと解釈されることがある。この記事では、誤解を防ぐため、ここでは広義の「時刻」を「日時」と表記する。これに対して、2つの日時の間（時間）を「期間」と呼ぶことにする。
Excelで行える期間の計算
期間を扱う計算には、大きく2つがある。1つは、「開始日時」と「終了日日時」から期間を求める計算、もう1つは、期間と特定の日時を組み合わせて、開始予定日時または終了予定日時を求めるものだ。
「Microsoft Excel（エクセル）」では、日付同士の引き算が可能で、終了日時から開始日時を引くことで「期間」を求めることができ、日付に「期間」を加減算することで開始日時や終了日時を求めることができる。
ただし、日付同士の引き算で、「終了日」を期間に含めたい場合には、「1」を足す必要がある点は忘れないでほしい。
日付の「文字列」で計算する場合は言語（ロケール）に注意
Excelは、明確に日付と解釈できる文字列（ダブルクオートで囲まれたもの）をシリアル値として解釈する機能があるため、式や関数の引数に文字列として日付を指定することもできる。DATE関数などで指定するよりも記述が短くなって分かりやすい。
ただ、この日付の解釈は、Excelの対応言語（ロケール）に影響され、ロケールごとに解釈できるパターンが異なる。例えば日本語版Excelでは「yyyy/mm/dd」の形式を解釈できる一方で、その他の表記では思わぬエラーが発生することがあるので、利用は慎重に行う必要がある。DATE関数を使って「年」「月」「日」をシリアル値に変換する方がエラーの心配がない。
稼働日や営業日の計算には専用の関数が使える
ビジネスでは、期間から休日や祝日などを除いた「稼働日（あるいは営業日）」で計算することが少なくない)。Excelでは、そのためにNETWORKDAYS関数やWORKDAY関数が用意されている。これらの関数では、土日などの1週間のうちの定期的な休みを「週末」、それ以外の祝日や組織独自の休み（夏期休業など）を「休日」と表記する。名称は「週末」だが、Excelでは土日以外の任意の曜日の休みとその組み合わせに対応できる。Excelの稼働日の計算では、終了日が含まれる点にも注意したい。
Excelのシリアル値と期間
Excelでは、前述の通り日付を「シリアル値」と呼ばれる形式で保持している。Excelでは日付のデータは、全てシリアル値として扱われる。このシリアル値に書式を設定することで、「2020年8月1日」などの表示が可能になる。「2020年8月1日」などの文字列をセルに入力した場合も、Excelはこれをシリアル値として記憶し、入力されたものと同じ書式で表示する。「2020/8/1」と日付を入力したら、セルの書式設定によって「44044」といったように数値（シリアル値）になってしまい、困った経験を持つ人もいることだろう。
これに対して「期間」は、単純な数値である。数値が何を意味しているのかは人間側の問題だが、シリアル値との計算を行う場合、数値は「日数」と解釈される。つまり、シリアル値に「1.0」を足すことは、日付を1日増やすことに相当する。
外部からテキストファイルなどで読み込まれたシートでは、シリアル値に変換されず文字列のままになる場合もある。ただ、前述のようにExcelには、文字列を日付として解釈する機能があるため、ロケールに適合した形式（日本語版ならyyyy/mm/ddなど）なら、文字列のままでも問題なく処理できる。
このシリアル値は、1900年1月1日0時0分0秒からの経過時間を表したものだ。ただし、24時間（1日）が1.0としてあり、時刻は、0.0（0時0分0秒）から1.0（翌日の0時0分0秒）の範囲で表現される。日時と日時の引き算を行うことで、2つの日時の間にある「期間」を計算できる（同様の計算はDAYS関数でも行えるが、利用できるのはExcel 2013以降）。
同様に1日を1.0とした数値をシリアル値と加算、減算することで、特定の日時の前後の日時を計算することも可能だ。もちろん、小数点以下を含むシリアル値を使えば、該当の日の特定の時刻を示すこともできる。
日にち単位、あるいは時間単位での日付の計算は、このようにシリアル値と「期間」の加算、減算で行える。しかし、あくまでも可能なのは、日にち単位および時間単位での加減算である。シリアル値から得られた「期間」を日にち／時間のままで扱う分には、問題は何もない。しかし、これをシリアル値として扱うのは間違いだ。
「期間」に「YEAR」「MONTH」「DAY」などのシリアル値を処理する関数を適用すれば、「年」「カ月」「日数」で表示できるように思える。Excelに関する解説記事などで簡易な期間の表現方法として紹介されることがある。しかし、これは1900年を基準にした「時刻」の表示であるため、期間の1〜31日は、日付の書式指定「m"カ月"」を使って「1カ月」と表示できる。「1900年1月1日〜1月31日」の1月だけを表示しているからだ。
MONTH関数を使って結果を「-1」すれば、一見よさそうに思えるが、このときの1カ月は、1月の31日が基準となってしまう。つまり、期間が32日になって始めて「1カ月」になる。この方法では、毎月の日数が変化してしまい、必ずしも期間を求めた開始日、終了日のカレンダーと一致するとは限らない。2月には、うるう年も含まれてしまうため、さらに不正確になる。
Excelは、本来平年となるべき1900年をうるう年＊1として処理するようになっているため、期間をシリアル値として処理しようとすると、必ず1900年の誤ったうるう年の1日が含まれてしまう。かつてExcelのライバルだったLotus 1-2-3では、1900年が平年となるため、1904年を基準値として利用していた。
*1 うるう年は、以下の条件で決まる。そのため、1900年は4で割り切れるものの、さらに100で割り切れるため、「平年」となる。
- 西暦年が4で割り切れる年はうるう年
- 西暦年が100で割り切れる年はうるう年ではない
- 西暦年が400で割り切れる年はうるう年
Excelには、正しく期間を扱う関数が用意されているため、期間をシリアル値として扱うことは、不正確な表示になるため、やめた方がよい。
「開始日」「終了日」から期間を求める
開始日、終了日から期間を日、月、年といった精度で求める場合の計算式は、下表のようになる。
| 精度 | 数式 |
|---|---|
| 日 | =終了日-開始日+1 |
| 時間 | =(終了日-開始日+1)*24 |
| 月 | =YEARFRAC(終了日,開始日)*12 |
| 年 | =YEARFRAC(終了日,開始日) |
| 年-月 | =TRUNC(YEARFRAC(終了日,開始日))&"年"&MOD(YEARFRAC(終了日,開始日),1)*12&"カ月" |
| 稼働日 | =NETWORKDAYS.INTL(開始日, 終了日, 週末, 休日) |
各関数の詳細はこの後に説明する。
※小数点数値の表示桁数などは別途ROUND関数などで指定する必要がある。
前述のように日数単位（小数点以下の時間を含む）でよければ、終了日から開始日を引き算することでその間の日数が求まる。ただし、開始日を含める場合には「1」を加算する。このとき、Excelはカレンダーを元に計算を行うため、うるう年などがあっても正しい日数を求めることが可能だ。
また、日付データ同士の結果はシリアル値として「1.0」が24時間に対応しているため、時間を含む日付データであっても、そのまま計算ができる。これを時間単位で表現したい場合には、「24」を掛けることで変換が可能だ。
月や年といった単位はYEARFRAC関数を使う
月や年といった単位で計算結果となる期間を表現したい場合には、YEARFRAC関数を使う。この関数は、開始日と終了日から年単位（1年が「1.0」）となる数値を求める。この関数は複数の日付計算ルールに対応しているが、何も指定しなければ、米国NASD方式（1カ月を30日として計算する方式）を使って計算が行われる。実際の日数を使いたい場合には、「基準」引数に「1」を指定する。
ここでは詳しく解説しないが、金融関係など、企業や業界などで期間計算のルールがNASD方式やヨーロッパ方式に定められている場合にはそれに従うが、特にルールが定められていない場合や一般的な稼働日を計算するときには「基準」に必ず「1」を設定して利用する。
| 開始日 | 終了日 | 基準 | YEARFRAC() | 説明 |
|---|---|---|---|---|
| 2020年1月02日 | 2021年7月31日 | 0 | 1.5805556 | 米国NASD方式による計算（30日／月、360日／年） |
| 2020年1月02日 | 2021年7月31日 | 1 | 1.5759234 | 実際の日数と実際の1年の長さ（365日／年または366日／年）で計算 |
| 2020年1月02日 | 2021年7月31日 | 2 | 1.6 | 実際の日数と360日／年から計算 |
| 2020年1月02日 | 2021年7月31日 | 3 | 1.5780822 | 実際の日数と365日／年から計算 |
| 2020年1月02日 | 2021年7月31日 | 4 | 1.5777778 | ヨーロッパ方式の30日／月、360日／年による計算 |
※ NASD National Association of Securities Dealers（全米証券業協会）。現FINRA（Financial Industry Regulatory Authority）
YEARFRAC関数を使った期間の計算方法
YEARFRAC関数は、開始日と終了日の間となる「期間」を年単位で求める。このとき、小数点以下の数値は、1年未満の期間を表しているため、これに12を掛ければ月単位に変換できる。Excelには小数部を取り出す専用の関数はなくMOD関数を使う。
YEARFRAC関数の結果に「12」を掛けることで月単位に直すことが可能だ。長期間にわたる計画などで、期間が年を超える場合、YEARFRAC関数の整数部と小数部を分離して計算することで「年」「カ月」の表示に変換することもできる。
稼働日にはNETWORKDAYS関数
稼働日の計算には、NETWORKDAYS関数を使う。各週の休日が土日以外ならばNETWORKDAYS.INTL関数を使う（ただし、NETWORKDAYS.INTL関数は、Excel 2010以降でしか利用できない）。ちなみに、この関数名は「Net Workdays」という意味である。ネットは、「グロス／ネット」として使われるときの意味で「正味の値」のことだ。
=NETWORKDAYS(開始日, 終了日, 休日）
=NETWORKDAYS.INTL(開始日, 終了日, 週末, 休日）
「休日」には、稼働日数に含めない日付のリストを配列またはセル参照で指定する（具体例は後述）。「週末」には、稼働日数に含めない曜日を指定する（詳細はすぐ後で説明）。
Copyright© Digital Advantage Corp. All Rights Reserved.