指定した月数後の日付を求め、休日の場合は日付を繰越して翌営業日を表示します。なお年末12月29から31日及び 1月2日から3日も祝日として扱うか指定できます。
仕事の処理日などの都合で、休日の場合は日付を繰越して平日の営業日を求めたい場合があると思いますので、そのような場合に利用してください。
2015年1月1日から2020年12月末までは内閣府祝日についての日付、それ以降は現行の法律で計算します。
2014年以前の祝日は計算できません、また2021年以降は現行法の計算ですので、「国民の祝日に関する法律」が改正された場合は変更になる可能性があります。
2019年2020年と祝日の日付が特殊なので2015年から2020年までは日付で祝日を指定していますのでコードが長くなってしまいました。
VBEの標準モジュールに下のコードを登録して「Excelマクロ有効フォーム」で保存してください。
関数の登録方法は⇒♯000 ユーザー定義関数を登録する方法
登録されたら、シート上からユーザー関数「 BusinessdayMA 」が使用できます。
使い方は
ワークシート上で = BusinessdayMA(日付,[月],［年末年始］) の関数として使用することで、祝日を含めた曜日を取得できます。
【例】
BusinessdayMA(日付,[月],［年末年始］)
Businessday MA (2020/1/3,1)→ 2020/2/3 1/3日の翌月の営業日2/3
BusinessdayMA(2019/11/30,1)→2020/1/6
年末29日から年始3日までを休日として扱う
BusinessdayMA(2019/11/30,1,0)→ 2019/12/30
年末の平日を平日として扱う
Function BusinessdayMA(D As Date, Optional N As Long = 0, Optional YEF As Long = 1) As Date
'指定した月数後の日付を求め、休日の場合は日付を繰越した翌営
'業日を求める関数
'年末年始コード省略及び1の場合は12月29から31日及び
'1月2日から3日も休日として扱います。
'2015年1月1日から2020年12月末までは内閣府祝日についての日付
'それ以降は現行の法律で計算
'2014年以前の祝日は計算できません、また2021年以降は現行法の
'計算ですので、「国民の祝日に関する法律」が改正された場合は
'変更になる可能性があります。
'BusinessdayMA(日付,[月],［年末年始］)
'月は指定しない場合は当月1なら翌月、-1の場合は前月
'年末年始は省略又は1の場合は12月29日から1月3日までは祝日扱い
'BusinessdayMA(2019/4/1,0)→ 2019/4/1
'BusinessdayMA(2019/4/1,1)→ 2019/5/7 5/1日の次の営業日5/7
'BusinessdayMA(2019/11/30,1)→2020/1/6
'年末29日から年始3日までを休日として扱う
'BusinessdayMA(2019/12/30,0,0)→ 2019/12/30
'年末の平日を平日として扱う
On Error GoTo EXITFUN
Dim PHoliday(1 To 200, 1 To 2)
Dim PHolidayT(1 To 22, 1 To 3)
Dim Y As Long
Dim M As Long
Dim Mdays As Long
Dim WFD As Long
Dim MD As Long
Dim HolidayC As Boolean
Dim i As Long
Dim j As Long
Dim k As Long: k = 1
Dim l As Long
Y = Year(D)
M = Month(D)
Mdays = DateSerial(Y, M + N + 1, 0) - DateSerial(Y, M + N, 0)
If Day(D) > Mdays Then
D = DateSerial(Y, M + N, Mdays)
Else
D = DateSerial(Y, M + N, Day(D))
End If
For i = 1 To 30
HolidayC = False
Y = Year(D)
If Y <= 2020 Then
PHoliday(1, 1) = #1/1/2015#: PHoliday(1, 2) = "元日"
PHoliday(2, 1) = #1/12/2015#: PHoliday(2, 2) = "成人の日"
PHoliday(3, 1) = #2/11/2015#: PHoliday(3, 2) = "建国記念の日"
PHoliday(4, 1) = #3/21/2015#: PHoliday(4, 2) = "春分の日"
PHoliday(5, 1) = #4/29/2015#: PHoliday(5, 2) = "昭和の日"
PHoliday(6, 1) = #5/3/2015#: PHoliday(6, 2) = "憲法記念日"
PHoliday(7, 1) = #5/4/2015#: PHoliday(7, 2) = "みどりの日"
PHoliday(8, 1) = #5/5/2015#: PHoliday(8, 2) = "こどもの日"
PHoliday(9, 1) = #5/6/2015#: PHoliday(9, 2) = "振替休日"
PHoliday(10, 1) = #7/20/2015#: PHoliday(10, 2) = "海の日"
PHoliday(11, 1) = #9/21/2015#: PHoliday(11, 2) = "敬老の日"
PHoliday(12, 1) = #9/22/2015#: PHoliday(12, 2) = "振替休日"
PHoliday(13, 1) = #9/23/2015#: PHoliday(13, 2) = "秋分の日"
PHoliday(14, 1) = #10/12/2015#: PHoliday(14, 2) = "体育の日"
PHoliday(15, 1) = #11/3/2015#: PHoliday(15, 2) = "文化の日"
PHoliday(16, 1) = #11/23/2015#: PHoliday(16, 2) = "勤労感謝の日"
PHoliday(17, 1) = #12/23/2015#: PHoliday(17, 2) = "天皇誕生日"
PHoliday(18, 1) = #1/1/2016#: PHoliday(18, 2) = "元日"
PHoliday(19, 1) = #1/11/2016#: PHoliday(19, 2) = "成人の日"
PHoliday(20, 1) = #2/11/2016#: PHoliday(20, 2) = "建国記念の日"
PHoliday(21, 1) = #3/20/2016#: PHoliday(21, 2) = "春分の日"
PHoliday(22, 1) = #3/21/2016#: PHoliday(22, 2) = "振替休日"
PHoliday(23, 1) = #4/29/2016#: PHoliday(23, 2) = "昭和の日"
PHoliday(24, 1) = #5/3/2016#: PHoliday(24, 2) = "憲法記念日"
PHoliday(25, 1) = #5/4/2016#: PHoliday(25, 2) = "みどりの日"
PHoliday(26, 1) = #5/5/2016#: PHoliday(26, 2) = "こどもの日"
PHoliday(27, 1) = #7/18/2016#: PHoliday(27, 2) = "海の日"
PHoliday(28, 1) = #8/11/2016#: PHoliday(28, 2) = "山の日"
PHoliday(29, 1) = #9/19/2016#: PHoliday(29, 2) = "敬老の日"
PHoliday(30, 1) = #9/22/2016#: PHoliday(30, 2) = "秋分の日"
PHoliday(31, 1) = #10/10/2016#: PHoliday(31, 2) = "体育の日"
PHoliday(32, 1) = #11/3/2016#: PHoliday(32, 2) = "文化の日"
PHoliday(33, 1) = #11/23/2016#: PHoliday(33, 2) = "勤労感謝の日"
PHoliday(34, 1) = #12/23/2016#: PHoliday(34, 2) = "天皇誕生日"
PHoliday(35, 1) = #1/1/2017#: PHoliday(35, 2) = "元日"
PHoliday(36, 1) = #1/2/2017#: PHoliday(36, 2) = "振替休日"
PHoliday(37, 1) = #1/9/2017#: PHoliday(37, 2) = "成人の日"
PHoliday(38, 1) = #2/11/2017#: PHoliday(38, 2) = "建国記念の日"
PHoliday(39, 1) = #3/20/2017#: PHoliday(39, 2) = "春分の日"
PHoliday(40, 1) = #4/29/2017#: PHoliday(40, 2) = "昭和の日"
PHoliday(41, 1) = #5/3/2017#: PHoliday(41, 2) = "憲法記念日"
PHoliday(42, 1) = #5/4/2017#: PHoliday(42, 2) = "みどりの日"
PHoliday(43, 1) = #5/5/2017#: PHoliday(43, 2) = "こどもの日"
PHoliday(44, 1) = #7/17/2017#: PHoliday(44, 2) = "海の日"
PHoliday(45, 1) = #8/11/2017#: PHoliday(45, 2) = "山の日"
PHoliday(46, 1) = #9/18/2017#: PHoliday(46, 2) = "敬老の日"
PHoliday(47, 1) = #9/23/2017#: PHoliday(47, 2) = "秋分の日"
PHoliday(48, 1) = #10/9/2017#: PHoliday(48, 2) = "体育の日"
PHoliday(49, 1) = #11/3/2017#: PHoliday(49, 2) = "文化の日"
PHoliday(50, 1) = #11/23/2017#: PHoliday(50, 2) = "勤労感謝の日"
PHoliday(51, 1) = #12/23/2017#: PHoliday(51, 2) = "天皇誕生日"
PHoliday(52, 1) = #1/1/2018#: PHoliday(52, 2) = "元日"
PHoliday(53, 1) = #1/8/2018#: PHoliday(53, 2) = "成人の日"
PHoliday(54, 1) = #2/11/2018#: PHoliday(54, 2) = "建国記念の日"
PHoliday(55, 1) = #2/12/2018#: PHoliday(55, 2) = "振替休日"
PHoliday(56, 1) = #3/21/2018#: PHoliday(56, 2) = "春分の日"
PHoliday(57, 1) = #4/29/2018#: PHoliday(57, 2) = "昭和の日"
PHoliday(58, 1) = #4/30/2018#: PHoliday(58, 2) = "振替休日"
PHoliday(59, 1) = #5/3/2018#: PHoliday(59, 2) = "憲法記念日"
PHoliday(60, 1) = #5/4/2018#: PHoliday(60, 2) = "みどりの日"
PHoliday(61, 1) = #5/5/2018#: PHoliday(61, 2) = "こどもの日"
PHoliday(62, 1) = #7/16/2018#: PHoliday(62, 2) = "海の日"
PHoliday(63, 1) = #8/11/2018#: PHoliday(63, 2) = "山の日"
PHoliday(64, 1) = #9/17/2018#: PHoliday(64, 2) = "敬老の日"
PHoliday(65, 1) = #9/23/2018#: PHoliday(65, 2) = "秋分の日"
PHoliday(66, 1) = #9/24/2018#: PHoliday(66, 2) = "振替休日"
PHoliday(67, 1) = #10/8/2018#: PHoliday(67, 2) = "体育の日"
PHoliday(68, 1) = #11/3/2018#: PHoliday(68, 2) = "文化の日"
PHoliday(69, 1) = #11/23/2018#: PHoliday(69, 2) = "勤労感謝の日"
PHoliday(70, 1) = #12/23/2018#: PHoliday(70, 2) = "天皇誕生日"
PHoliday(71, 1) = #12/24/2018#: PHoliday(71, 2) = "振替休日"
PHoliday(72, 1) = #1/1/2019#: PHoliday(72, 2) = "元日"
PHoliday(73, 1) = #1/14/2019#: PHoliday(73, 2) = "成人の日"
PHoliday(74, 1) = #2/11/2019#: PHoliday(74, 2) = "建国記念の日"
PHoliday(75, 1) = #3/21/2019#: PHoliday(75, 2) = "春分の日"
PHoliday(76, 1) = #4/29/2019#: PHoliday(76, 2) = "昭和の日"
PHoliday(77, 1) = #4/30/2019#: PHoliday(77, 2) = "国民の休日"
PHoliday(78, 1) = #5/1/2019#: PHoliday(78, 2) = "即位の日"
PHoliday(79, 1) = #5/2/2019#: PHoliday(79, 2) = "国民の休日"
PHoliday(80, 1) = #5/3/2019#: PHoliday(80, 2) = "憲法記念日"
PHoliday(81, 1) = #5/4/2019#: PHoliday(81, 2) = "みどりの日"
PHoliday(82, 1) = #5/5/2019#: PHoliday(82, 2) = "こどもの日"
PHoliday(83, 1) = #5/6/2019#: PHoliday(83, 2) = "振替休日"
PHoliday(84, 1) = #7/15/2019#: PHoliday(84, 2) = "海の日"
PHoliday(85, 1) = #8/11/2019#: PHoliday(85, 2) = "山の日"
PHoliday(86, 1) = #8/12/2019#: PHoliday(86, 2) = "振替休日"
PHoliday(87, 1) = #9/16/2019#: PHoliday(87, 2) = "敬老の日"
PHoliday(88, 1) = #9/23/2019#: PHoliday(88, 2) = "秋分の日"
PHoliday(89, 1) = #10/14/2019#: PHoliday(89, 2) = "体育の日"
PHoliday(90, 1) = #10/22/2019#: PHoliday(90, 2) = "即位礼正殿の儀"
PHoliday(91, 1) = #11/3/2019#: PHoliday(91, 2) = "文化の日"
PHoliday(92, 1) = #11/4/2019#: PHoliday(92, 2) = "振替休日"
PHoliday(93, 1) = #11/23/2019#: PHoliday(93, 2) = "勤労感謝の日"
PHoliday(94, 1) = #1/1/2020#: PHoliday(94, 2) = "元日"
PHoliday(95, 1) = #1/13/2020#: PHoliday(95, 2) = "成人の日"
PHoliday(96, 1) = #2/11/2020#: PHoliday(96, 2) = "建国記念の日"
PHoliday(97, 1) = #2/23/2020#: PHoliday(97, 2) = "天皇誕生日"
PHoliday(98, 1) = #2/24/2020#: PHoliday(98, 2) = "振替休日"
PHoliday(99, 1) = #3/20/2020#: PHoliday(99, 2) = "春分の日"
PHoliday(100, 1) = #4/29/2020#: PHoliday(100, 2) = "昭和の日"
PHoliday(101, 1) = #5/3/2020#: PHoliday(101, 2) = "憲法記念日"
PHoliday(102, 1) = #5/4/2020#: PHoliday(102, 2) = "みどりの日"
PHoliday(103, 1) = #5/5/2020#: PHoliday(103, 2) = "こどもの日"
PHoliday(104, 1) = #5/6/2020#: PHoliday(104, 2) = "振替休日"
PHoliday(105, 1) = #7/23/2020#: PHoliday(105, 2) = "海の日"
PHoliday(106, 1) = #7/24/2020#: PHoliday(106, 2) = "スポーツの日"
PHoliday(107, 1) = #8/10/2020#: PHoliday(107, 2) = "山の日"
PHoliday(108, 1) = #9/21/2020#: PHoliday(108, 2) = "敬老の日"
PHoliday(109, 1) = #9/22/2020#: PHoliday(109, 2) = "秋分の日"
PHoliday(110, 1) = #11/3/2020#: PHoliday(110, 2) = "文化の日"
PHoliday(111, 1) = #11/23/2020#: PHoliday(111, 2) = "勤労感謝の日"
ElseIf Y > 2020 Then
PHolidayT(1, 1) = DateSerial(Y, 1, 1): PHolidayT(1, 2) = "元日": PHolidayT(1, 3) = 1
PHolidayT(2, 1) = DateSerial(Y, 1, 2): PHolidayT(2, 2) = "年始休み": PHolidayT(2, 3) = 0
PHolidayT(3, 1) = DateSerial(Y, 1, 3): PHolidayT(3, 2) = "年始休み": PHolidayT(3, 3) = 0
WFD = Weekday(DateSerial(Y, 1, 1))
If 2 < WFD Then
MD = 8 + 2 - WFD + (2 - 1) * 7
ElseIf WFD <= 2 Then
MD = 1 + 2 - WFD + (2 - 1) * 7
End If
PHolidayT(4, 1) = DateSerial(Y, 1, MD): PHolidayT(4, 2) = "成人の日": PHolidayT(4, 3) = 1
PHolidayT(5, 1) = DateSerial(Y, 2, 11): PHolidayT(5, 2) = "建国記念の日": PHolidayT(5, 3) = 1
PHolidayT(6, 1) = DateSerial(Y, 2, 23): PHolidayT(6, 2) = "天皇誕生日": PHolidayT(6, 3) = 1
PHolidayT(7, 1) = DateSerial(Y, 3, Int(20.8431 + 0.242194 * (Y - 1980)) _
- Int((Y - 1980) / 4)): PHolidayT(7, 2) = "春分の日": PHolidayT(7, 3) = 1
PHolidayT(8, 1) = DateSerial(Y, 4, 29): PHolidayT(8, 2) = "昭和の日": PHolidayT(8, 3) = 1
PHolidayT(9, 1) = DateSerial(Y, 5, 3): PHolidayT(9, 2) = "憲法記念日": PHolidayT(9, 3) = 0
PHolidayT(10, 1) = DateSerial(Y, 5, 4): PHolidayT(10, 2) = "みどりの日": PHolidayT(10, 3) = 0
PHolidayT(11, 1) = DateSerial(Y, 5, 5): PHolidayT(11, 2) = "こどもの日": PHolidayT(11, 3) = 1
WFD = Weekday(DateSerial(Y, 7, 1))
If 2 < WFD Then
MD = 8 + 2 - WFD + (3 - 1) * 7
ElseIf WFD <= 2 Then
MD = 1 + 2 - WFD + (3 - 1) * 7
End If
PHolidayT(12, 1) = DateSerial(Y, 7, MD): PHolidayT(12, 2) = "海の日": PHolidayT(12, 3) = 1
PHolidayT(13, 1) = DateSerial(Y, 8, 11): PHolidayT(13, 2) = "山の日": PHolidayT(13, 3) = 1
WFD = Weekday(DateSerial(Y, 9, 1))
If 2 < WFD Then
MD = 8 + 2 - WFD + (3 - 1) * 7
ElseIf WFD <= 2 Then
MD = 1 + 2 - WFD + (3 - 1) * 7
End If
PHolidayT(14, 1) = DateSerial(Y, 9, MD): PHolidayT(14, 2) = "敬老の日": PHolidayT(14, 3) = 1
PHolidayT(16, 1) = DateSerial(Y, 9, Int(23.2488 + 0.242194 * (Y - 1980)) - _
Int((Y - 1980) / 4)): PHolidayT(16, 2) = "秋分の日": PHolidayT(16, 3) = 1
If PHolidayT(14, 1) + 2 = PHolidayT(16, 1) Then
PHolidayT(15, 1) = PHolidayT(14, 1) + 1: PHolidayT(15, 2) = "国民の休日": PHolidayT(15, 3) = 1
End If
WFD = Weekday(DateSerial(Y, 10, 1))
If 2 < WFD Then
MD = 8 + 2 - WFD + (2 - 1) * 7
ElseIf WFD <= 2 Then
MD = 1 + 2 - WFD + (2 - 1) * 7
End If
PHolidayT(17, 1) = DateSerial(Y, 10, MD): PHolidayT(17, 2) = "スポーツの日": PHolidayT(17, 3) = 1
PHolidayT(18, 1) = DateSerial(Y, 11, 3): PHolidayT(18, 2) = "敬老の日": PHolidayT(18, 3) = 1
PHolidayT(19, 1) = DateSerial(Y, 11, 23): PHolidayT(19, 2) = "勤労感謝の日": PHolidayT(19, 3) = 1
PHolidayT(20, 1) = DateSerial(Y, 12, 29): PHolidayT(20, 2) = "年末休み": PHolidayT(20, 3) = 0
PHolidayT(21, 1) = DateSerial(Y, 12, 30): PHolidayT(21, 2) = "年末休み": PHolidayT(21, 3) = 0
PHolidayT(22, 1) = DateSerial(Y, 12, 31): PHolidayT(22, 2) = "年末休み": PHolidayT(22, 3) = 0
For j = 1 To 22
If YEF <> 0 Or PHolidayT(i, 3) <> 0 Then
If PHolidayT(i, 1) <> "" Then
PHoliday(j, 1) = PHolidayT(i, 1)
PHoliday(j, 2) = PHolidayT(i, 2)
j = j + 1
If Weekday(PHolidayT(i, 1)) = 1 And PHolidayT(i, 3) = 1 Then
PHoliday(j, 1) = PHolidayT(i, 1) + 1
PHoliday(j, 2) = "振替休日"
j = j + 1
End If
'憲法記念日の振替休日
If i = 9 And Weekday(PHolidayT(i, 1)) = 1 Then
PHoliday(j, 1) = PHolidayT(i, 1) + 3
PHoliday(j, 2) = "振替休日"
j = j + 1
End If
'みどりの日の振替休日
If i = 10 And Weekday(PHolidayT(i, 1)) = 1 Then
PHoliday(j, 1) = PHolidayT(i, 1) + 2
PHoliday(j, 2) = "振替休日"
j = j + 1
End If
End If
End If
Next
End If
For l = 1 To 111
If PHoliday(l, 1) <> "" And D = PHoliday(l, 1) Then
HolidayC = True
Exit For
End If
Next
If D <> 0 And YEF = 1 And (D <= DateSerial(Y, 1, 3) Or D >= DateSerial(Y, 12, 29)) Then
HolidayC = True
End If
If D <> 0 And Weekday(D) = 1 Then
HolidayC = True
ElseIf D <> 0 And Weekday(D) = 7 Then
HolidayC = True
End If
If HolidayC = True Then
D = D + 1
Else
Exit For
End If
Next
BusinessdayMA = D
EXITFUN:
End Function
ここで紹介したコード使用による損害に対しては一切責任は負えません。