2020/06/05

2つの日付間の平日の日数計算




1.背景

業務には必ず開始日と終了日があります。その日付がたとえ予定や希望であっても、日割り計算をして出来る見通しが有るか否かを確認し日程修正等につなげていきます。但し働く側としても休みは必要なので「開始日と終了日の間に、平日は何日あるか」の計算が必要になります。
今回は、その計算方法についての紹介です。

2.計算の概要

平日の計算の前に「開始日と終了日の間は何日か」が必要です。詳細は後述しますが、今回は3種(手法1~3)を紹介します。
また「平日の日数」は、ワークシート関数を使ったもの(手法4~7)とマクロを使ったもの(手法8~11)を紹介します。
図2-1は、その手法をシート上に並べたものです。(一番下の添付ファイルの画面)

図2-1

3.2つの日付間の日数

3-1.手法1(ワークシート関数:DAYS )

ワークシート関数「DAYS(終了日, 開始日)」は、Microsoftでは「2 つの日付間の日数を返す」と説明しています。

開始日・終了日はシリアル値でなくとも、ダブルクォーテーションで囲った文字列(例:"2020/6/1")で入力してもOKです。また、シリアル値・文字列のチャンポンでも大丈夫です。
なお引数の順序は「終了日」「開始日」となっており、一般的な考え方とは逆になっているので注意が必要です。

それより問題は計算結果です。あなたは「開始日が6月1日、終了日が6月30日。その日数は?」と問われたら、何と答えますか? 私なら「6月1日も6月30日も期間内なので30日」と答えますが、DAYSの出す答えは「29日」です。

時刻まで含めて「開始が6月1日の午前0時 で 終了が6月30日の午前0時 なら29日間」とMicrosoft側は考えているのかもしれませんが、一方で手法4で紹介する「NETWORKDAYS」関数では「開始日と終了日を含めて計算」しており、考え方が一定していない様な気がします。

いずれにしても、DAYS関数を使用する際は、注意が必要です。

3-2.手法2(ワークシート関数:DATEDIF )

ワークシート関数「DATEDIF(開始日,終了日,単位)」は、「2 つの日付の間の日数、月数、年数を計算する」ものです。

開始日・終了日はシリアル値でなくとも、ダブルクォーテーションで囲った文字列(例:"2020/6/1")で入力してもOKです。また、シリアル値・文字列のチャンポンでも大丈夫です。
第三引数の「単位」には、図3-1から選択して入力します。今回は「期間の日数」ですので「"D"」を選択しています。
 単位 戻り値
"Y"期間の年数
"M"期間の月数
"D"期間の日数
"MD"開始日から終了日までの日数。日付の月数および年数は無視(Microsoftが、使用を推奨していない)
"Ym"開始日から終了日までの月数。日付の日数および年数は無視
"YD"開始日から終了日までの日数。日付の年数は無視
図3-1

この「DATEDIF」関数は「年齢」を求めるのによく使われる関数で、「DATEDIF(生まれた日,今日の日付,”Y”)」で「今日時点(「今日の日付」を「ある特定の日」に変えれば、その時点)での年齢」が得られます。

しかし日数を求める際には、DAYS関数と同じで「一般的な感覚からすると1日不足」しますので注意が必要です。

3-3.手法3(ワークシート関数:単純計算 )

Excelが扱う日付は「1900年1月1日をシリアル値= 1 」として数え始め、その間隔は「1日間(=24時間)を1」としています。
ですので「終了日 - 開始日 +1」は、「終了日のシリアル値」から「開始日のシリアル値」を引き算し、「開始日と終了日は、その期間に含める」考えから「+1」をすることで期間を求めています。

図で説明する程ではありませんが、手法1~3を表現すると図3-2の様になります。

図3-2

4.2つの日付間の平日の日数(ワークシート関数)

4-1.手法4(ワークシート関数:NETWORKDAYS )

NETWORKDAYS(開始日, 終了日, [祭日])」は、第一引数:開始日と第二引数:終了日は他の関数と同じですが、省略可能な第三引数に「平日にはしない日」のリストを渡すことで、「土日以外の祭日等を引いた平日の日数」を得ることができます。
単純に「土日を除く日数」を求めるのであれば、第一引数・第二引数のみを指定すればOkです。

またNETWORKDAYS関数は、DAYS関数やDATEDIF関数とは異なり、「開始日・終了日を含んだ日数」で計算します。

ちなみに第三引数に与えるリストですが、他サイトでは「Excelのシートに祭日一覧を作り・・・」と説明されていますが、私の経験からすると「毎年、祭日リストをメンテナンスするのは、結構大変な作業」の為、すぐにシステムが使われなくなってしまいます(私の努力不足だったからかもしれませんが)。
会社で共有できるリストを作って参照したり、ネットが参照できる環境でしたら、内閣府サイトの国民の休日関係(https://www8.cao.go.jp/ chosei/shukujitsu/gaiyou.html)の中にある祭日リスト「https://www8.cao.go.jp/ chosei/shukujitsu/syukujitsu.csv」を引っ張ってきて計算するのも良いかと思います。

4-2.手法5(ワークシート関数:NETWORKDAYS.INTL )

NETWORKDAYS.INTL(開始日, 終了日, [週末], [休日])」は、第一引数:開始日と第二引数:終了日は他の関数と同じです。第四引数の休日は、「NETWORKDAYS関数の第三引数の祭日と同じ」です。

第三引数の週末は、「週の内、何曜日を休日とするか」を指定するものです。週末番号か文字列を指定し、週末番号の場合には図4-1から選択をします。
週末番号週末の曜日
1 又は 省略土曜日と日曜日
2日曜日と月曜日
3月曜日と火曜日
4火曜日と水曜日
5水曜日と木曜日
6木曜日と金曜日
7金曜日と土曜日
 
週末番号週末の曜日
11日曜日のみ
12月曜日のみ
13火曜日のみ
14水曜日のみ
15木曜日のみ
16金曜日のみ
17土曜日のみ
図4-1

また、週末を文字列で表す場合は、月・火・水・木・金・土・日の順で「0は稼働日」「1は休日」で2進数のように表します。例えば土日月が休みであれば「1000011」とします。

4-3.手法6(ワークシート関数:WEEKDAYを使ったもの1 )

手法6は「=(終了日+7-開始日+1)-(ROUNDUP((終了日+7-(開始日+(6-IF(WEEKDAY(開始日,2)=7,0,WEEKDAY(開始日,2))))+1)/7,0))-(ROUNDUP((終了日+7-(開始日+(7-WEEKDAY(開始日,2)))+1)/7,0))-5」です。

式は①~④の大きく4つに分かれており、「①-②ー③-④」で全体の式になります。
① (終了日+7-開始日+1)
② (ROUNDUP((終了日+7-(開始日+(6-IF(WEEKDAY(開始日,2)=7,0,WEEKDAY(開始日,2))))+1)/7,0))
③ (ROUNDUP((終了日+7-(開始日+(7-WEEKDAY(開始日,2)))+1)/7,0))
④ 5
この内、赤字部分(+7 及び )を除いたもので説明していきます。(赤字部分は、開始から終了までが短期の場合の計算エラーを防ぐもので、追加する理由も含めて後で説明します)

まず①の「終了日-開始日+1」は、手法3の「開始日から終了日までの、開始・終了の日を含めた期間」になります。

次の②「ROUNDUP((終了日-(開始日+(6-IF(WEEKDAY(開始日,2)=7,0,WEEKDAY(開始日,2))))+1)/7,0)」については、図4-2で説明します。

図4-2

まず図4-2の左図のような対象日数があるとします。ワークシート関数WEEKDAYの第二引数に「2」を設定すると「月曜始まり(=日曜終わり)」の値が得られますが、手法6ではこの月曜始まりを使用します。
また、開始日は木曜日、終了日も木曜日と仮定します。

最終的にやりたいことは、図4-2の右図の「土曜日を開始日とした時、対象期間を7で割った値を切り上げた値」を計算することです。この値が「対象期間内の土曜日の数」になります。
(最終日がもし金曜であれば、ちょうど割り切れることになります。)

図4-2の中央図で、開始日(木曜日)の曜日番号は「4」(第二引数=2のため)ですので、開始日を土曜日にズラすためには「6(土曜日の曜日番号)-開始日の曜日番号」を開始日に足せば良いことが分かります。
そのズラした開始日から終了日までの期間の計算は「((終了日)-(開始日)+1」の開始日を置き換えて、「(終了日)-(開始日+(6― WEEKDAY(開始日,2)))+1」となります。

その修正した期間を7で割り、小数点以下を切り上げする「ROUNDUP(対象期間/7 , 0 )」ことで「対象期間の土曜日の数」を得ることができます。

なお、開始日が日曜日だったときのことを図4-3で考えてみます。

図4-3

開始日が日曜ですと、曜日番号は「7」になります。先程の「6(土曜日の曜日番号)-開始日の曜日番号」に当てはめると「6-7」になり、「開始日を1日前にする」ことになってしまいます。
この「開始日を1日前倒し」をしてしまうと、図4-3の右図で分かるとおり「土曜日を1日多くカウントしてしまう」ことになります。

そのため「開始日が日曜の場合は、開始日から6日分ズラす」ことが必要ですので、先程の式「6(土曜日の曜日番号)-開始日の曜日番号」の「開始日の曜日番号」に「0」を置き換えれば良い事になります。これをIF文を使って表せば「IF(WEEKDAY(開始日,2)=7,0,WEEKDAY(開始日,2))」となります。

ちなみに開始日が土曜の場合は、「6-WEEKDAY(開始日,2)」がゼロとなり、開始日をズラさないことになります。

以上で、②「ROUNDUP((終了日-(開始日+(6-IF(WEEKDAY(開始日,2)=7,0,WEEKDAY(開始日,2))))+1)/7,0)」が対象期間の土曜日の数を表していることがわかりました。


続いて③「ROUNDUP((終了日-(開始日+(7-WEEKDAY(開始日,2)))+1)/7,0)」です。
薄々お分かりかと思いますが、これは「対象期間内の日曜日の数」を表しています。図4-4で説明します。

図4-4

土曜日の時と同様に開始日を「7(日曜日の曜日番号)-開始日の曜日番号」の分だけ後ろにずらし、開始日を日曜に修正した上で対象期間を計算します。あとは7で割って切り上げれば日曜日の数が取得できます。

なお、土曜日の方で行ったような「開始日が日曜だったら・・・」のIF文は、日曜日計算では不要です。なぜなら「日曜日が曜日番号で最後だから」です。開始日を後ろにズラしているつもりが前になるような事は起こりません。

これで「①(全体の期間)ー②(土曜日の数)ー③(日曜日の数)」で、平日の日数が取得できます。

では、最初に除いた赤字の部分の説明です。
開始日と終了日の間が短かった時を考えてみます。例えば図4-5のように開始が木曜、終了が緊要ではどうなるでしょうか。

図4-5

本当は「期間内には土曜も日曜もない」ので、図4-5の場合でしたら「2」となるはずです。
しかし計算上では開始日と終了日が逆転してしまうことになり、図4-5の右図(日曜日の計算)では「日曜の数=-1」が得られてしまい、最終的には「3」となってしまいます。
土曜日の計算では「0」と都合の良い値が得られている様に見えますが、たまたま終了日が金曜であった為です。終了日が木曜であれば、土曜日日数計算でも-1が出てしまいます。

対象期間が短いために「終了日を開始日が追い越してしまう」のですから、「終了日に7日(1週間)を足し」て計算する事で、図4-6の様に「終了日を開始日が追い越す事が無く」なります。
図4-6

なお、足した7日の中には必ず土日が入っていますので、計算結果としては「平日が5日増えてしまう」事になります。そこで計算結果から最後④に5を引きます。
ということで、「終了日」→「終了日+7」にし、最後に「-5」することで、「対象期間が短くても正しい平日日数が計算できる」ことになります。

4-4.手法7(ワークシート関数:WEEKDAYを使ったもの2 )

手法7は「=(終了日+7-開始日+1)-(ROUNDUP((終了日+7-開始日-(7-WEEKDAY(開始日))+1)/7,0))-(ROUNDUP((終了日+7-開始日-(8-IF(WEEKDAY(開始日)=1,8,WEEKDAY(開始日)))+1)/7,0))-5」です。
ほとんど手法6と同じですが、WEEKDAY関数の第二引数を省略しているため、週を日曜から数えています。
一応手法6と同様の図4-7で説明します。

図4-7

土曜日の日数計算では、日曜始まりのWEEKDAY関数では土曜日は7になりますので、開始日をズラす量は「7-WEEKDAY(開始日)」となります。それ以降は手法6と同じです。

また開始日が土曜の場合は、「7ーWEEKDAY(開始日)」の計算がゼロになり開始日はそのままです。
開始日が日曜の場合は「7ーWEEKDAY(開始日)」の計算は「6」となり、6日後ろにズラすことでOKです。

次に、日曜日の日数計算ですが、日曜始まりのWEEKDAY関数では日曜日は1になります。
ですので開始日をズラす量としては図4-8の様に「8-WEEKDAY(開始日)」となります。

図4-8

開始日が土曜の場合は「8-WEEKDAY(開始日)」が「1」となり、開始日を1日後ろにズラし日曜始まりで計算できます。
しかし開始日が日曜の場合は、WEEKDAY(開始日)の値が1になるため、元の計算式「8-WEEKDAY(開始日)」が「7」 となり、「1週間短く」する事になってしまいます。

そこで、図4-9のように、開始日が日曜の場合は「開始日を移動させない」様に「IF(WEEKDAY(開始日)=1,8,WEEKDAY(開始日))」としています。

図4-9

最後に、手法6と同じく開始と終了の期間が短い場合は計算間違いが発生しますので、終了日に7日を足して、最後に平日日数の5を引いています。


尚、「手法6」と「手法7」でほぼ同じ様な計算式を作ってしまいましたが、少し言い訳をします。
WEEKDAY関数の第二引数に2を設定する「月曜始まりの曜日番号」を使うと、「計算対象の土日を週末に寄せられる」ために計算式が簡単になるのでは、と思いながら手法6を組み立てました。しかし残念ながら、土曜の数と日曜の数を数える方法しか思いつかず、手法7と同じような式となってしまいました。
今後、より簡単な数式を考え付いたらご紹介したいと思います。

5.2つの日付間の平日の日数(マクロ)

5-1.手法8(マクロ:WorksheetFunctionを使用 )

ワークシート関数の中にNETWORKDAYSという便利な関数が存在するため、それをマクロ中でWorksheetFunctionオブジェクトのメソッドとして使用したのが図5-1になります。
  1. '========== ⇩① WroksheetFunctionオブジェクトを使用 ============
  2. Function myWeekdays1(Start_day As Date, End_day As Date, Optional Holiday As Variant) As Long
  3.  myWeekdays1 = Application.WorksheetFunction.NetworkDays (Start_day, End_day, Holiday)
  4.  'myWeekdays1 = Application.WorksheetFunction. NetworkDays_Intl(Start_day, End_day, , Holiday)
  5. End Function
図5-1

図5-1は単純で、WorksheetFunctionオブジェクトのNetworkDaysメソッドに引数をそのまま渡しているだけです。
NetworkDaysメソッドには、開始日・終了日の次の第三引数には、ワークシート上の祭日一覧の範囲や配列を指定できます。図5-2は、祭日(相当)を配列にして第三引数に与えた例です。
  1. '========== ⇩② 祭日リストを配列データにして平日日数を算出 ============
  2. Sub test()
  3.  Dim Holiday As Variant
  4.  Holiday = Array("2020/6/5", "2020/6/4", "2020/6/3")
  5.  MsgBox myWeekdays1("2020/6/1", "2020/6/11", Holiday)
  6. End Sub
図5-2

実はMicrosoftのサイトには、第三引数には「日付を表すシリアル番号の配列定数」をとの記載がありますが、図5-2のように文字列の日付でも大丈夫そうです。

また、図5-1の4行目は見え消しにしていますが、「NetworkDays_Intl」メソッドを使っても同じ結果が得られます。
尚、ワークシート関数では「NETWORKDAYS.INTL」とピリオドで繋がっていますが、WorksheetFunctionオブジェクトで使用する際はアンダースコア「 _ 」で繋ぎます。

5-2.手法9(マクロ:全対象日の曜日を調べる )

開始日から終了日までの期間の全ての日付の曜日を調べるのが図5-3です。
  1. '========== ⇩③ 全対象日の曜日を調べるマクロ ============
  2. Function myWeekdays2(Start_day As Date, End_day As Date) As Long
  3.  Dim cnt As Long
  4.  Dim Temp As Long
  5.  Temp = End_day - Start_day + 1
  6.  For cnt = 1 To Temp
  7.   Select Case Weekday(Start_day + cnt - 1)
  8.    Case 1, 7
  9.     Temp = Temp - 1
  10.   End Select
  11.  Next cnt
  12.  myWeekdays2 = Temp
  13. End Function
図5-3

16行目の右辺「終了日ー開始日+1」で、対象期間の日数を算出し、変数Tempに代入します。
17~22行目のFor~Nextで、日付を1日ずつズラし、18行目で曜日を調べています。カウント変数cntを1からスタートさせていますので、関数Weekdayの引数は「Start_day + cnt - 1」と-1を付けています。

Weekday関数の第二引数を省略していますので、日曜=1の日曜始まりの週番号になります。日曜が1、土曜が7ですので19行目で1と7の時だけ拾い上げています。
土日の場合は、20行目で「Temp = Temp - 1」と対象期間の日数から1ずつ引いていき、全ての対象日付を調べ終わると土日を除いた平日の日数が残ることになります。

最後にその残った平日の日数を、23行目でFunctionの戻り値にしています。

この手法は単純で分かり易いですが、期間の日数分だけはFor~Nextを回さないといけないことになります。
(たいした処理時間では無いと思いますが)

5-3.手法10(マクロ:週数を計算し、その端数分の曜日を調べる )

手法9では全ての日付の曜日を調べていましたが、例えば「100日=14週+2日」ですので「週の数を最初に求めて、残った日付だけ曜日を調べる」というのが図5-4です。
  1. '========== ⇩④ 全対象日の曜日を調べるマクロ ============
  2. Function myWeekdays3(Start_day As Date, End_day As Date) As Long
  3.  Dim cnt As Long
  4.  Dim Temp As Long
  5.  Dim Temp2 As Long   ←週の数を入れる変数
  6.  Dim Temp3 As Long   ←端数の日付数を入れる変数
  7.  Temp = End_day - Start_day + 1
  8.  Temp2 = Int(Temp / 7)
  9.  Temp3 = Temp - Temp2 * 7
  10.  For cnt = 1 To Temp3
  11.   Select Case Weekday(End_day - cnt + 1)
  12.    Case 1, 7
  13.     Temp = Temp - 1
  14.   End Select
  15.  Next cnt
  16.  myWeekdays3 = Temp - Temp2 * 2
  17. End Function
図5-4

この手法の考え方は、図5-5の様になっています。

図5-5

32行目で開始日から終了日までの期間日数を取得します。
33行目で、その期間日数を7で割り、小数点以下を切り捨てた値を取得します。これが週の数で、図5-5の中央図で言うと「3」の値になります。
34行目では、全期間日数から週数x7を引き、端数の日数を求めます。
この端数の日付には土日が含まれているのか否か分かりませんので、35~40行目のFor~Nextで曜日を調べます。
もし土曜(曜日番号7)か日曜(曜日番号1)であれば38行目「Temp = Temp - 1」が実行されます。
最後に41行目で、端数日付の中の土日の日数を除いた変数Tempから、週数x2(週の中には、土曜と日曜が必ず1つずつ入っているから)を引いた値が全体の平日数になりますので、それをFunctionの戻り値にします。

この方法は手法9よりもFor~Nextで回す回数が少なくて済みます(最高で6回) ちなみに、33行目で割り切れてしまった時には、変数Temp3はゼロになります。
35行目のFor文も「For cnt = 1 To 0 」となるため、For~Next文は1回も実行されずに41行目にジャンプする事になります。
(この「For cnt = 1 To 0 」は、ちょっとカッコよく無いな と思っています。)

5-4.手法11(マクロ:日曜~土曜の週数を調べ、前後の残りは曜日で判断する )

日曜~土曜を1週とし、その塊が何週あるかを調べます。その前後の残りには開始日と終了日が含まれていますので、その開始日・終了日の曜日により休日が何日含まれるかを計算するのが、図5-6になります。
このマクロは、For~Nextが無いのが特徴です。
  1. '========== ⇩⑤ 日曜~土曜の週数を調べ、前後の残りは曜日で判断 ============
  2. Function myWeekdays4(Start_day As Date, End_day As Date) As Long
  3.  Dim Temp As Long
  4.  Dim Temp2 As Long
  5.  Dim start_week As Integer,end_week As Integer
  6.  Temp = End_day - Start_day + 1
  7.  start_week = Weekday(Start_day)
  8.  end_week = Weekday(End_day)
  9.  If start_week > 1 Then
  10.   Temp = Temp - 1
  11.   Start_day = Start_day + (8 - start_week)
  12.  End If
  13.  If end_week < 7 Then
  14.   Temp = Temp - 1
  15.   End_day = End_day - (end_week)
  16.  End If
  17.  Temp2 = Int((End_day - Start_day + 1) / 7)
  18.  myWeekdays4 = Temp - Temp2 * 2
  19. End Function
図5-6

48行目で、開始日から終了日までの期間日数を調べるのは、他マクロと一緒です。
51行目で開始日の曜日を調べ、52行目で終了日の曜日を調べます。

54~56行目は、「開始日の曜日が1を超えたら」つまり「開始日が日曜日以外」だったら、55~56行目を実行します。
まず55行目では、「(開始日は日曜では無いので)開始日の週には土曜日が1つだけある」ことから、期間日数の変数Tempから1を引きます。
そして56行目で「Start_day = Start_day + (8 - start_week)」と、開始日を次の日曜日に修正します。

58~61行目は終了日についての修正です。「終了日が7未満」つまり「終了日土曜日以外」だったら、59行目で「(終了日は土曜では無いので)終了日の週には日曜日が1つだけある」ことから、期間日数の変数Tempから1を引きます。
そして60行目で「End_day = End_day - (end_week)」と、終了日を前の土曜日に修正します。

これを図で表したのが図5-7です。

図5-7

「開始日が日曜以外の週」及び「終了日土曜日以外の週」を除く週は「日曜から土曜までの週」ですので、その週には日曜日が1つ、土曜日が1つ含まれています。

では「開始日が日曜」の場合は、どうなるでしょう。図5-8の左図の様に、「開始日の週には、日曜が1つと土曜が1つ有る」ことになり、開始日を移動する必要もありません。
「終了日が土曜」の場合も図5-8の右図のように同様です。

図5-8

そのようにして「修正した開始日(開始日が日曜の場合は、元の開始日)」と「修正した終了日(終了日が土曜の場合は、元の終了日)」を使って、63行目で週の数を計算し、変数Temp2に代入します。
前にも説明した通り、この変数Temp2の各週には、日曜1つ・土曜1つが含まれています。

65行目では、開始日を含む週と終了日を含む週で計算した変数Tempから、完全な週の変数Temp2x2(日曜1+土曜1)を引いて、Functionの戻り値にしています。

6.開始日・終了日の入れ替え可否について

尚、「開始日」と「終了日」が入れ替わってしまうと、各手法での結果は以下の様になります。

図6-1

正しい「開始日」「終了日」での結果に対し、-1を掛けたものになっているのは「手法1、手法4、手法5、手法8」で、どれも専用のワークシート関数(手法8はWorksheetFunction)を使ったものだけです。さすがは専用関数だと思います。
もし入れ替わることがあり得る場合は、マクロ関数の入口で2つの引数値の大きさを調べ、逆転していれば入れ替えて計算し、最後に-1を描ける 等の処置が必要です。

7.最後に

日付関数として「DAYS」「DATEDIF」がExcel関数辞典に載り、各サイトでも紹介されています。「Microsoftが提供してくれている関数だから安心」などと思って使用すると、自分の思った通りの結果が得られない事にもなります。
世の中の要望を元にMicrosoftが関数を作っているのであって、世の中(自分)の常識=世の中の要望では無いのです。

また「DAYS360」という関数も良く紹介されていますが、これも「1年を360日、1月を30日で計算する」という欧米で使用されているらしい方式での関数で、日本ではあまり一般的では無い考え方のようです。
但し契約書で「1年を360日とし・・・」等と記載されている場合には有用です。

どの関数でも同様ですが、「自分のやりたい事に合っているか」を調べ、都度確認しながら使用することが必要だと思います。


2つの日付間の平日の日数計算(it-029.xlsm)

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル(Excel等)のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。(2022年4月より切替開始)
解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。