祝日を判定する関数 、EXCELでセルの値が祝日かどうか計算したい場合、その日付が祝日の場合TRUE、平日の場合FALSEに変換できます。この関数を使うとカレンダー等に祝日を表示したい場合などに利用できます。
2015年1月1日から2020年12月末までは内閣府祝日についての日付、それ以降は現行の法律で計算します。
2014年以前の祝日は計算できません、また2021年以降は現行法の計算ですので、「国民の祝日に関する法律」が改正された場合は変更になる可能性があります。
2019年2020年と祝日の日付が特殊なので2015年から2020年までは日付で祝日を指定していますのでコードが長くなってしまいました。
VBEの標準モジュールに下のコードを登録して「Excelマクロ有効フォーム」で保存してください。
関数の登録方法は⇒♯000 ユーザー定義関数を登録する方法
登録されたら、シート上からユーザー関数「 PHolidayC」が使用できます。
使い方は
ワークシート上で = PHolidayC( 日付) の関数として使用することで、祝日を判定を取得できます。
【例】
PHolidayC(2015/4/3)→ FALSE
PHolidayC(2015/4/29)→ TRUE
PHolidayC(2019/6/1)→ TRUE 土曜を休日として扱う
PHolidayC(2019/12/30)→ TRUE 年末を休日をして扱う
PHolidayC(2019/12/30,0)→ FALSE 年末の平日を平日として扱う
Function PHolidayC(D As Date, Optional YEF As Long = 1) As Boolean
’祝日かどうかの判定する関数。
’ 年末年始コード省略及び1の場合は12月29から31日及び
’ 1月2日から3日も祝日として扱います。
’ 2015年1月1日から2020年12月末までは内閣府祝日についての日付
’ それ以降は現行の法律で計算
’ 2014年以前の祝日は計算できません、また2021年以降は現行法の
’ 計算ですので、「国民の祝日に関する法律」が改正された場合は
’ 変更になる可能性があります。
’ PHolidayC(日付,［年末年始］)
’ 年末年始は省略又は1の場合は12月29日から1月3日までは祝日扱い
’ PHolidayC(2015/4/3)→ FALSE
’ PHolidayC(2015/4/29)→ TRUE
’ PHolidayC(2019/6/1)→ TRUE 土曜を休日として扱う
’ PHolidayC(2019/12/30)→ TRUE 年末を休日をして扱う
’ PHolidayC(2019/12/30,0)→ FALSE 年末の平日を平日として扱う
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 WFD As Long
Dim MD As Long
Dim i1 As Long
Dim i2 As Long: i2 = 1
Dim i3 As Long
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) = 1
PHolidayT(10, 1) = DateSerial(Y, 5, 4): PHolidayT(10, 2) = “みどりの日”: PHolidayT(10, 3) = 1
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 i1 = 1 To 22
If YEF <> 0 Or PHolidayT(i1, 3) <> 0 Then
If PHolidayT(i1, 1) <> “” Then
PHoliday(i2, 1) = PHolidayT(i1, 1)
PHoliday(i2, 2) = PHolidayT(i1, 2)
i2 = i2 + 1
If Weekday(PHolidayT(i1, 1)) = 1 And PHolidayT(i1, 3) = 1 Then
PHoliday(i2, 1) = PHolidayT(i1, 1) + 1
PHoliday(i2, 2) = “振替休日”
i2 = i2 + 1
End If
End If
End If
Next
End If
For i3 = 1 To 200
If PHoliday(i3, 1) <> “” And D = PHoliday(i3, 1) Then
PHolidayC = 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
PHolidayC = True
End If
EXITFUN:
End Function
ここで紹介したコード使用による損害に対しては一切責任は負えません。