方法A:曜日判定と祝日表検索
土日、および、祝日表の日にち以外を営業日とします。
=IF(AND(MID("0000011",WEEKDAY(B49,2),1)="0",ISNA(VLOOKUP(B49,祝日表,1,FALSE))),"○","")
指定日の曜日が
休みでなく、
休日に該当しない(指定日が休日表に見つからない)場合は営業日と判定。
・
MID("0000011",WEEKDAY(B49,2),1)="0"
月曜を基点とした曜日数字(月曜〜日曜:1〜7)を
インデックスとして、休日を表す文字列から取り出した1文字が"0"であれば営業日
休日を表す文字列("0000011")
WEEKDAY関数から帰る値は、1〜7の7種類
それぞれを月火水目金土日 に相当させ、"0"は営業日,"1"は休みを表します。
本例では、土曜と日曜の部分を"1"とし、休みにしています。
MID は、文字列の指定位置から指定数の文字列を取り出す関数
本方法は、休み曜日が
視覚的にわかりやすくした方法。下記のように記述してもい可。
=IF(AND(WEEKDAY(B49,2)<=5,ISNA(VLOOKUP(B49,祝日表,1,FALSE))),"○","")
WEEKDAYの部分は、曜日数値が5(金曜日)以下(月〜金)かを判定しています。
ただし、
月曜と
木曜が休みの場合には個別判定が必要となるので、式が
長くなる。
(本例方式では、"
1001000"とするだけでよい。)
・
ISNA(VLOOKUP(B49,祝日表,1,FALSE))) : 祝日表に見つからない
方法B:「期間内の営業日数を算出する関数」を利用
指定期間内の
営業日数を返巣関数、
NETWORKDAYS.INTL を利用します。
=IF(NETWORKDAYS.INTL(B49,B49,1,祝日表),"○","")
期間内(開始日〜終了日)の営業日数を返すので、
当日(
当日から当日まで)の営業日数が1(TRUE)であれば
営業日と判定できます。
NETWORKDAYS.INTL(開始日, 終了日, [週末], [休日])
開始日、終了日:日数を計算する 2 つの日付を指定
週末: 開始日と終了日の間の稼働日数に含めない、週末の曜日を指定。
"週末" は、週末を示す週末番号または文字列で指定。
週末番号で指定する場合:
1、省略:土日が休み
2:日曜と月曜 3:月曜日と火曜日 4:火曜日と水曜日 〜略〜
11:日曜のみ 12:月曜のみ 13:火曜のみ 〜略〜
文字列で指定する場合:
週末の文字列値は 7 文字で、文字列内の各文字は月曜日から始まる各曜日を表ます。
1 は非稼働日を表し、0 は稼働日を表します。
たとえば、0000011 と指定すると土曜日と日曜日が週末になります
休日:休日のリスト範囲を指定(本例では名前範囲"祝日表"を使用)
方法C:「指定日数後の営業日を返す関数」を利用
開始日から起算して、指定された営業(稼動)日数だけ前または後の日付に対応する日付を返す関数
WORKDAY.INTL を利用します。
=IF(WORKDAY.INTL(B49-1,1,"0000011",祝日表)=B49,"○","")
当日の一日前(B49-1)から
1営業日後の日付が、当日と
等しければ、当日は営業(稼働)日となります。
A,B,Cの中で一番簡素で短く、旧バージョンのエクセルでも使用できるので「
推奨」解です。
週内固定休が土日でない場合は、方法Bを推奨
(開始日, 日数, [週末], [休日])
開始日: 必須。 起算日を表す日付を指定。
日数: 必須。 開始日から起算して、週末や祭日を除く週日の日数を指定。
正負指定可。0は不可
週末: 省略可。 週末にする曜日 (稼働日と見なされない日) を指定。
"週末" は、週末を示す週末番号または文字列で指定。
指定方法は、上記の NETWORKDAYS.INTL と同じ
休日: 省略可。 国民の祝日や夏期休暇など、稼働日数の計算から除外する日付のリストを指定
補足
祝日表
指定の休日(創立記念日、県民休日等)がある場合は、"祝日表"に追加しておきます。
2000年〜2069年の祝日表は「
祝日一覧表」を参照
INTL
関数の後に付く INTL は International の略
一般化して使用できるようにした関数を表しています。
EXCEL2010以降で使用可能
週末は国によって異なります。
日本の場合、カレンダー的には日曜基点で土曜日が週末(気分的/慣習的には日曜が週末)
ヨーロッパは月曜基点で日曜が週末
WORKDAY , NETWORKDAYS
問題
支払
締め日を毎月
15日(セルE6)とします。
口座からの
引き落とし日は、支払日が
15日以前の場合は
翌月末、
16日以降の場合は
翌々月末とします。
ただし、月末が
営業日(土日祝日を除く)でない場合は、以降の最初の
営業日を引き落とし日とします。
祝日は「
祝日表」を使用。範囲(I6:I45)には"祝日表"の名前を定義済み
引き落とし予定日を表示してください。
完成例
航空券:15日以前なので翌月(12月)の末
洋服:16日以降なので翌々月(1月)の末
ライコスx10:翌々月(2月)の末は土曜日なので、その次の営業日(=2020/03/2(月))
解答例
=IF(DAY(D47)<=$E$6,WORKDAY(EOMONTH(D47,1)-1,1,祝日表 ),WORKDAY(EOMONTH(D47,2)-1,1,祝日表))
式の意味
支払日の日にち(DAY(D47))を判定
締日以前の場合
翌月末の1日前から次の営業日を求めます。
上記の場合、11月の翌月末は12月31日。その1日前は12月30日
よって、その次の営業日は12月31日(火曜日)となります。
もし、次の日が営業日でない場合(「絵画(東山魁夷)」の例)は、以降の直近の営業日となります。
「絵画(東山魁夷)」の例では、2月31日は土曜、3月1日は日曜なので、次の営業日は3月2日の月曜日となります。
締日より後の場合
翌々月末以降の営業日となります。