[[20191120175513]]　『WORKDAY関数を応用して特定の土日を例外としたい』（さわ）　ページの最後に飛ぶ

『WORKDAY関数を応用して特定の土日を例外としたい』（さわ）

お知恵をお貸しください！

WORKDAY関数を使用して、2営業日先を納品日として算出しています。

年末年始など、特定の土日を営業日として納品日を自動算出させたいです。

例）11/23（土）を営業日とする

WORKDAY関数の開始日を11/21とし、2営業日先を自動算出しようとした場合、
11/23（土）と算出されるようにしたい。

お力添えのほど、よろしくお願いいたします。

＜使用 Excel：Excel2007、使用 OS：Windows10 ＞

 土日しか話題に出てこないですが、祝日の扱いはどうなっていますか？

（半平太） 2019/11/20(水) 19:12

（半平太）さん

失礼しました！
祝日は、一覧化しWORKDAY関数に組み込んでおります！
（さわ） 2019/11/20(水) 19:59

 名前定義で「祝日」と「例外日」があるものとします。

 祝日と例外日の両リストに載っている日は出勤とします。

 B1セル =A1+SMALL(MOD(SIGN((WEEKDAY(A1+{0,1,2,3,4,5,6,7})<6)*(COUNTIF(祝日,A1+{1,2,3,4,5,6,7,8})=0)+COUNTIF(例外日,A1+{1,2,3,4,5,6,7,8}))-1,99)+{1,2,3,4,5,6,7,8},2)

 下にコピー

 <結果図>
  行  _______A_______  _______B_______
   1  2019/11/21(木)   2019/11/23(土) 
   2  2019/11/22(金)   2019/11/25(月)

（半平太） 2019/11/20(水) 20:29　修正20:56

（半平太）さん
ありがとうございます！
数式の内容がまだ把握できてませんが、希望通りの日付が算出されるようになりました！
ちなみに、2営業日先の定義を3営業日などに変更する際は、どの個所を変更すれば良いでしょうか？
（さわ） 2019/11/21(木) 10:14

 ＞ちなみに、2営業日先の定義を3営業日などに変更する際は、どの個所を変更すれば良いでしょうか？

 先が長い場合は、まず、これらの配列定数をもっと多目にする(提示の式は８歴日以内にヒットするとタカをくくっています）
 　　　　　　　　　　　　　↓
 　　　　　　　　{0,1,2,3,4,5,6,7}
 　　　　　　　　{1,2,3,4,5,6,7,8}

 　　　　　　　　　その後、ここを任意の営業日数に変える 
  　　　　　　　　　　　　　↓
 B1セル =A1+Small・・・・・,2)

（半平太） 2019/11/21(木) 10:25

（半平太）さん
早速お返事いただきありがとうございます！
営業日の変更箇所、理解いたしました！

＞配列定数をもっと多目にする

この意味がまだよくわかってなくて、ご説明いただけると嬉しいです。

理解力がなくてホント申し訳ありません！
（さわ） 2019/11/21(木) 10:31

（半平太）さん
何度もホント申し訳ありません！

同じように例外日を設定して、今度は、指定日から2日前を算出しようとします。
該当日が、土日祝であった場合は、前営業日。
該当日が、土日祝の例外日であった場合は、その日。

こういった条件を、教えていただいた数式を変更し設定する事は可能でしょうか？
（さわ） 2019/11/21(木) 14:20

 遡るケースもありですかぁ・・、ユーザー定義関数を作った方がよさそう。

 (1) B1セル =WorkdayBwFw(A1,-2,祝日,例外日)
 (2) B2セル =WorkdayBwFw(A2, 2,祝日,例外日)

 <結果図>
  行  _______A_______  _______B_______
   1  2019/11/21(木)   2019/11/19(火) 
   2  2019/11/21(木)   2019/11/23(土)

 ’標準モジュールにコピペ
 　　↓
 Function WorkdayBwFw(rDate As Range, Dys As Long, rHolid As Range, rExcp As Range)
     Dim App As Application
     Dim NN As Long
     Dim baseDT As Date
     Dim BwFw As Long
     Dim daysSofar As Long

     If Dys = 0 Then
         WorkdayBwFw = rDate
         Exit Function
     Else
         Set App = Application

         BwFw = IIf(Dys < 0, -1, 1)

         For NN = BwFw To 400 * BwFw Step BwFw
             baseDT = rDate + NN

             If App.CountIf(rExcp, baseDT) Then
                 daysSofar = daysSofar + 1
             ElseIf App.WorkDay(baseDT - BwFw, BwFw, rHolid) = baseDT Then
                 daysSofar = daysSofar + 1
             End If

             If daysSofar = Abs(Dys) Then
                 WorkdayBwFw = baseDT
                 Exit Function
             End If
         Next
     End If
 End Function

（半平太） 2019/11/21(木) 18:58

（半平太）さん
凄いです！
内容は、難しくて理解できませんが、望んでいたとおりの事が出来るようになりました！
本当にありがとうございました！

（さわ） 2019/11/21(木) 20:02

（半平太）さん
何度もすみません！

B1セル =WorkdayBwFw(A1,-3,祝日,例外日)

11/26をA1に入力するとB1に11/21が表示されました。
3営業日前でなく、3日前が土日祝（もしくは例外日）であったら、
一番近い営業日（例ですと11/22（金））を表示させる事は可能でしょうか？

頼ってばかりで申し訳ございません。。。
（さわ） 2019/11/21(木) 20:46

 ＞ Function WorkdayBwFw(rDate As Range,   Dys As Long, rHolid As Range, rExcp As Range)
 　　　　　　　　　　　　　　　　型をVariantに変える
 　　　　　　　　　　　　　　　　　↓　　
  　Function WorkdayBwFw(rDate As Variant, Dys As Long, rHolid As Range, rExcp As Range)

 ＞B1セル =WorkdayBwFw(A1,-3,祝日,例外日)

 　B1セル =WorkdayBwFw(A1-2,-1,祝日,例外日)
                       　↑  ↑
       　　　　　　　   -3+1 ｜
                             ｜
 　　　　　　　　　　　　-1だけ残す

（半平太） 2019/11/21(木) 21:43

（半平太）さん
ホント凄いです！
全て無事に解決しました！
本当にありがとうございました！
（さわ） 2019/11/23(土) 12:42

 たまに出る質問なので、コードを少しテコ入れしておきます。

 Function WorkdayBwFw(vDate As Variant, Dys As Long, rHolid As Range, rExcp As Range)
     Dim App As Application
     Dim NN As Long
     Dim baseDT As Long
     Dim BwFw As Long
     Dim daysSofar As Long

     vDate = Int(vDate)

     If Dys = 0 Then
         WorkdayBwFw = vDate
         Exit Function
     Else
         Set App = Application

         BwFw = IIf(Dys < 0, -1, 1)
         NN = 0
         Do
             NN = NN + BwFw
             baseDT = vDate + NN

             If App.CountIf(rExcp, baseDT) Then
                 daysSofar = daysSofar + 1
             ElseIf App.NetworkDays(baseDT, baseDT, rHolid) = 1 Then
                 daysSofar = daysSofar + 1
             End If

             If daysSofar = Abs(Dys) Then
                 WorkdayBwFw = baseDT
                 Exit Function
             End If
         Loop
     End If
 End Function

（半平太） 2019/11/23(土) 14:05

コメント返信：

[ 一覧(最新更新順) ]

[[20191120175513]] 『WORKDAY関数を応用して特定の土日を例外としたい』（さわ） ページの最後に飛ぶ

[[20191120175513]]　『WORKDAY関数を応用して特定の土日を例外としたい』（さわ）　ページの最後に飛ぶ