曜日を表示する関数 、日曜日～土曜日の曜日を表示します、祝日の場合は祝日と表示します。なお年末12月29から31日及び ‘1月2日から3日も祝日として扱うか指定できます。カレンダー等で祭日も含めた日付の曜日を表示したい場合などに使用できます。
2015年1月1日から2020年12月末までは内閣府祝日についての日付、それ以降は現行の法律で計算します。
2014年以前の祝日は計算できません、また2021年以降は現行法の計算ですので、「国民の祝日に関する法律」が改正された場合は変更になる可能性があります。
2019年2020年と祝日の日付が特殊なので2015年から2020年までは日付で祝日を指定していますのでコードが長くなってしまいました。
VBEの標準モジュールに下のコードを登録して「Excelマクロ有効フォーム」で保存してください。
関数の登録方法は⇒♯000 ユーザー定義関数を登録する方法
登録されたら、シート上からユーザー関数「WeekdayH」が使用できます。
使い方は
ワークシート上で = WeekdayH(日付,［年末年始］,[曜日のフォーマット])の関数として使用することで、祝日を含めた曜日を取得できます。
曜日の表示および祝日はフォーマットで選択します。
年末年始のフォーマット
省略又は1の場合は12月29日から1月3日までは祝日扱い
曜日のフォーマット
0 1(日)～7(土)の整数、祝日は8で表示
1 “日”～”土”の文字、祝日は”祝”で表示
2 “日曜日”～”土曜日”の文字、祝日は”祝日”で表示
【例】
WeekdayH(2015/4/3)→ 金
WeekdayH(2015/4/3,1,2)→ 金曜日
WeekdayH(2019/4/29,0,0)→ 8
曜日コード1日曜日～7土曜日、8祝日
WeekdayH(2019/4/29,0,1)→ 祝
WeekdayH(2015/12/29,0,2)→ 火曜日
WeekdayH(2016/1/2,1,1)→ 祝
Function WeekdayH(D As Date, Optional YEF As Long = 1, Optional F As Long = 0)
'WeekdayH(日付,［年末年始］,[曜日のフォーマット])
'曜日の表示をする関数、曜日の表示および祝日はフォーマットで
'選択します。
'年末年始は省略又は1の場合は12月29日から1月3日までは祝日扱い
'曜日のフォーマット
'0 1(日)～7(土)の整数、祝日は8で表示
'1 "日"～"土"の文字、祝日は"祝"で表示
'2 "日曜日"～"土曜日"の文字、祝日は"祝日"で表示
'2015年1月1日から2020年12月末までは内閣府祝日についての日付
'それ以降は現行の法律で計算
'2014年以前の祝日は計算できません、また2021年以降は現行法の
'計算ですので、「国民の祝日に関する法律」が改正された場合は
'変更になる可能性があります。
'WeekdayH(2015/4/3)→ 金
'WeekdayH(2015/4/3,1,2)→ 金曜日
'WeekdayH(2019/4/29,0,0)→ 8
'曜日コード1日曜日～7土曜日、8祝日
'WeekdayH(2019/4/29,0,1)→ 祝
'WeekdayH(2015/12/29,0,2)→ 火曜日
'WeekdayH(2016/1/2,1,1)→ 祝
On Error GoTo EXITFUN
Dim PHoliday(1 To 200, 1 To 2)
Dim PHolidayT(1 To 22, 1 To 3)
Dim WeekdayF(1 To 8, 0 To 2)
Dim Y As Long
Dim WFD As Long
Dim MD As Long
Dim WD As Long
Dim i1 As Long
Dim i2 As Long: i2 = 1
Dim i3 As Long
Dim i4 As Long
If D = 0 Then
GoTo EXITFUN
End If
Y = Year(D)
WD = Weekday(D)
WeekdayF(1, 1) = "日": WeekdayF(1, 2) = "日曜日"
WeekdayF(2, 1) = "月": WeekdayF(2, 2) = "月曜日"
WeekdayF(3, 1) = "火": WeekdayF(3, 2) = "火曜日"
WeekdayF(4, 1) = "水": WeekdayF(4, 2) = "水曜日"
WeekdayF(5, 1) = "木": WeekdayF(5, 2) = "木曜日"
WeekdayF(6, 1) = "金": WeekdayF(6, 2) = "金曜日"
WeekdayF(7, 1) = "土": WeekdayF(7, 2) = "土曜日"
WeekdayF(8, 1) = "祝": WeekdayF(7, 2) = "祝日"
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 111
If PHoliday(i3, 1) <> "" And D = PHoliday(i3, 1) Then
WD = 8
Exit For
End If
Next
If D <> 0 And YEF = 1 And (D <= DateSerial(Y, 1, 3) Or D >= DateSerial(Y, 12, 29)) Then
WD = 8
End If
If F = 0 Then
WeekdayH = WD
Else
For i4 = 1 To 8
If i4 = WD Then
WeekdayH = WeekdayF(i4, F)
End If
Next
End If
Exit Function
EXITFUN:
WeekdayH = ""
End Functionここで紹介したコード使用による損害に対しては一切責任は負えません。