Excle VBA 指定期間の稼働日数を取得する

スポンサーリンク

稼働日数を取得する方法

土曜や日曜、祝日などの休みを除いた日数を稼働日といいますが、Excel2010以降では、稼働日数を算出する際に便利な「NETWORKDAYS.INTL」関数が用意されています。

このワークシート関数はVBAでも利用できるようになっています。VBAで利用する場合は、WorksheetFunction【ワークシートファンクション】オブジェクトに用意されているNetworkdays_Intl【ネットワークデイズインテル】メソッドを使用します。

Networkdays_Intl【ネットワークデイズインテル】メソッド

指定した期間内の指定した休日を除いた稼働日を取得するにはWorksheetFunction【ワークシートファンクション】オブジェクトのNetworkdays_Intl【ネットワークデイズインテル】メソッドを使用します。

Networkdays_Intl【ネットワークデイズインテル】メソッドの書式と設定値の説明

[]内は省略可能です。
オブジェクト.Networkdays_Intl( 開始日, 終了日, [休日,][祝日] )
【戻り値】倍精度浮動小数点型 (Double)

  • オブジェクト(必須)
    WorksheetFunction【ワークシートファンクション】オブジェクトを指定します。
  • 開始日(必須)
    集計する期間の起算日を指定します。
  • 終了日(必須)
    集計する期間の終了日を指定します。
  • 休日(省略可)
    稼働日に含めない週末の曜日を週末を表す文字列値または、週末番号で指定します。
    週末を表す文字列は「0を稼働曜日」「1を非稼働曜日」で表し、月曜日を起点に「0」と「1」で曜日の稼働曜日、非稼働曜日を表します。(例)土日を休日とする場合は「”0000011″」で表します。※省略した場合は土曜日と日曜日が非稼働日になります。

    週末番号一覧表
    週末番号 週末の曜日
    1 または省略 土曜日と日曜日
    2 日曜日と月曜日
    3 月曜日と火曜日
    4 火曜日と水曜日
    5 水曜日と木曜日
    6 木曜日と金曜日
    7 金曜日と土曜日
    11 日曜日のみ
    12 月曜日のみ
    13 火曜日のみ
    14 水曜日のみ
    15 木曜日のみ
    16 金曜日のみ
    17 土曜日のみ
  • 祝日(省略可)
    稼働日に含めない祝日のリストを指定します。セルに祝日の日付リストを作成してセル範囲を参照する方法が一般的ですがArray関数日付リテラルを配列に格納して参照する方法もあります。

インプットボックスを使用して稼働日をメッセージボックスに表示するコードと解説

土日と祝日を稼働日に含めないコード例です。祝日リストを祝日シートに作成します。

Sub 稼働日土日祝()
Dim 開始日 As Date
Dim 終了日 As Date
Dim 稼働曜日 As String
Dim 最終行 As Long
Dim 祝日 As Range
最終行 = Sheets("祝日").Cells(Rows.Count, 1).End(xlUp).Row
Set 祝日 = Range(Cells(2, 1), Cells(最終行, 1))
On Error GoTo エラー処理
開始日 = InputBox("開始日を2017/1/1形式で入力してください。")
終了日 = InputBox("終了日を2017/1/1形式で入力してください。")
稼働曜日 = "0000011"
MsgBox "日数は" & DateDiff("d", 開始日, 終了日) + 1 & "日です。" & vbCrLf & _
"稼働日数は" & WorksheetFunction.NetworkDays_Intl(開始日, 終了日, 稼働曜日, 祝日)
Exit Sub
エラー処理:
MsgBox "日付の入力が正しくありません。"
End Sub
2行目【Dim 開始日 As Date】
InputBox【インプットボックス】関数でユーザーから受け取る期間の起算日を格納する変数「開始日」を日付型(Date)で宣言します。


3行目【Dim 終了日 As Date】
InputBox【インプットボックス】関数でユーザーから受け取る期間の終了日を格納する変数「終了日」を日付型(Date)で宣言します。


4行目【Dim 稼働曜日 As String】
Networkdays_Intl【ネットワークデイズインテル】メソッドの引数「休日」で使用する週末を表す文字列を格納する変数「稼働曜日」を文字列型(String)で宣言します。


5行目【Dim 最終行 As Long】
シート祝日にある祝日のリストの最終行を格納する変数「最終行」を長整数型(Long)で宣言します。


6行目【Dim 祝日 As Range】
祝日が入力されているセル範囲を格納する変数「祝日」をオブジェクト型(Range)で宣言します。


7行目【最終行 = Sheets(“祝日”).Cells(Rows.Count, 1).End(xlUp).Row】
シート祝日の祝日リストの最終行を取得して変数「最終行」に代入します。


8行目【Set 祝日 = Range(Cells(2, 1), Cells(最終行, 1))】
祝日リストのセル範囲を取得して、変数「祝日」に代入します。オブジェクト型変数なので
Set【セゥット】キーワードを使用して代入します。


9行目【On Error GoTo エラー処理】
ユーザーが期間を入力するインプットボックスにDate型(日付)以外の値を入力するとエラーになるので、OnErrorステートメントとGoToステートメントを使用してエラーになった場合、16行目の「エラー処理:」ラベルまで処理をジャンプして処理を終了します。


10行目【開始日 = InputBox(“開始日を2017/1/1形式で入力してください。”)】
InputBox【インプットボックス】関数を使用してユーザーに開始日を入力してもらい値を変数「開始日」に代入します。


11行目【終了日 = InputBox(“終了日を2017/1/1形式で入力してください。”)】
InputBox【インプットボックス】関数を使用してユーザーに期間の終了日を入力してもらい値を変数「終了日」に代入します。


12行目【稼働曜日 = “0000011”】
引数「休日」に指定する週末を表す文字列を変数「稼働曜日」に代入します。この例では土日を週末として非稼働日としています。土日の場合は引数を省略できますが、非稼働の曜日を変更する場合があることを想定して記述しています。非稼働曜日を変更する場合はこの文字列を変更します。


13行目~14行目【MsgBox “日数は” & DateDiff(“d”, 開始日, 終了日) + 1 & “日です。” & vbCrLf & _
“稼働日数は” & WorksheetFunction.NetworkDays_Intl(開始日, 終了日, 稼働曜日, 祝日)】

ユーザーが入力した期間の日数をも求めるため指定した期間の間隔を取得するDateDiff【デイトディフ】関数を使用して期間の間隔を取得して1日を加算することにより指定期間の日数を取得します。Networkdays_Intl【ネットワークデイズインテル】メソッドの引数に各変数を設定して稼働日を取得し、MagBox【メッセージボックス】関数を使用して取得した値を表示します。

実行結果




セルに期間を入力してセルに日数と稼働日を取得するコードと解説

B1セルに開始日、B2セルに終了日を入力してマクロを実行するとD2セルに日数、E2セルに稼働日数を返すコード例です。



Sub 稼働日土日祝セル()
Dim 開始日 As Date
Dim 終了日 As Date
Dim 稼働曜日 As String
Dim 最終行 As Long
Dim 祝日 As Range
Dim 日数 As Variant
Dim 稼働日 As Variant
Sheets("祝日").Activate
最終行 = Cells(Rows.Count, 1).End(xlUp).Row
Set 祝日 = Range(Cells(2, 1), Cells(最終行, 1))
Sheets("稼働日").Activate
On Error GoTo エラー処理
開始日 = Range("B1").Value
終了日 = Range("B2").Value
稼働曜日 = "0000011"
Range("D2:E2").NumberFormatLocal = "G/標準"
Range("D2").Value = DateDiff("d", 開始日, 終了日) + 1
Range("E2").Value = WorksheetFunction.NetworkDays_Intl(開始日, 終了日, 稼働曜日, 祝日)
Exit Sub
エラー処理:
MsgBox "日付の入力が正しくありません。"
End Sub
基本的には上記のInputBox関数とMsgBox関数を使用した例と同じですが、16行目の【Range(“D2:E2”).NumberFormatLocal = “G/標準”】で値が返るセルの表示形式をNumberFormatLocal【ナンバーフォマットローカル】プロパティで「標準」に指定しないとExcelが自動的に日付形式に変更して正しい値が取得できなくなります。

実行結果


以上で、指定期間の稼働日数を取得するについての解説を終了します。ありがとうございました。

スポンサーリンク

関連記事・広告