Last active
December 5, 2020 16:00
-
-
Save ooltcloud/30644a3ce497319e5d50 to your computer and use it in GitHub Desktop.
日本の休み (祝日判定)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
'------------------------------------------------------------------ | |
' Excel VBA (標準モジュール) | |
' 日付を指定すると休みの属性に応じて、祝/休/振/日/土、を戻します。平時は空文字。 | |
'------------------------------------------------------------------ | |
' 諸注意 | |
' 2019年,2020,2021 年に対応しました。 | |
' ①春分の日と秋分の日は計算するのが面倒なので、Wikipedia から予想日をコピペしています。(汗 | |
' 前年に官報で発表される日付と違っていた場合は、適宜修正してください。 | |
' ②春分の日と秋分の日は 2000 年から設定していますが、他の祝日は 2013 年以前の状況を反映しません。(例:みどりの日) | |
' あくまで今年 2014 年以降の表示用のプログラムです。過去の祝日を正確に反映したい場合は、修正が必要です。 | |
' ③祝日法が改正されたら、適宜修正してください。 (未来は予想できません) | |
' ④おまけで「非公式な休日」を足しています。会社とかの休みを反映したい場合は場合はここへ記述します。 | |
' 以下のプログラムだとサンプルで盂蘭盆の日程とかが入っています。不要な場合は当該ルーチン、および呼び出しを削除してください。 | |
' また「世間は祝日だがうちの会社は休みじゃない」など、祝日を打ち消すロジックは用意していません(汗 | |
'------------------------------------------------------------------ | |
Public Function 日本の休み(vInDate As Date) As String | |
Dim ret As String | |
' 祝日判定 | |
ret = 日本の祝日(vInDate) | |
If ret <> "" Then | |
日本の休み = "祝" | |
Exit Function | |
End If | |
' 国民の休日判定 | |
If is国民の休日(vInDate) = True Then | |
日本の休み = "休" | |
Exit Function | |
End If | |
' 振替休日判定 | |
If is振替休日(vInDate) = True Then | |
日本の休み = "振" | |
Exit Function | |
End If | |
' 非公式の休日 (盆とか県民の日とか会社の休日) / 必要に応じて | |
'ret = 非公式の休日(vInDate) | |
'If ret <> "" Then | |
' 日本の休み = ret | |
' Exit Function | |
' | |
'End If | |
' 土日判定 | |
Select Case (Weekday(vInDate)) | |
Case 1: 日本の休み = "日" | |
Case 7: 日本の休み = "土" | |
Case Else: 日本の休み = "" | |
End Select | |
End Function | |
'------------------------------------------------------------------ | |
Private Function 日本の祝日(vInDate As Date) As String | |
Dim strRet As String | |
strRet = "" | |
' 2000年から2030年のまでの春分/秋分の一覧表 | |
' (Wkipediaより http://ja.wikipedia.org/wiki/%E7%A7%8B%E5%88%86%E3%81%AE%E6%97%A5) | |
Dim 春分 As Variant | |
春分 = Array(20, 20, 21, 21, 20, 20, 21, 21, 20, 20, 21, 21, 20, 20, 21, 21, 20, 20, 21, 21, 20, 20, 21, 21, 20, 20, 20, 21, 20, 20, 20) | |
Dim 秋分 As Variant | |
秋分 = Array(23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 22, 23, 23, 23, 22, 23, 23, 23, 22, 23, 23, 23, 22, 23, 23, 23, 22, 23, 23) | |
' 要素分解 | |
Dim intyear As Integer: intyear = Year(vInDate) | |
Dim intday As Integer: intday = Day(vInDate) | |
Dim intMonth As Integer: intMonth = Month(vInDate) | |
Dim intWeekDay As Integer: intWeekDay = Weekday(vInDate) | |
Dim intWeek As Integer: intWeek = (intday - 1) \ 7 + 1 | |
' 月日固定 | |
If (intMonth = 1) And (intday = 1) Then strRet = "元日" | |
If (intMonth = 2) And (intday = 11) Then strRet = "建国記念の日" | |
If (intMonth = 2) And (intday = 23) And (intyear >= 2020) Then strRet = "天皇誕生日" | |
If (intMonth = 4) And (intday = 29) Then strRet = "昭和の日" | |
If (intMonth = 5) And (intday = 3) Then strRet = "憲法記念日" | |
If (intMonth = 5) And (intday = 4) Then strRet = "みどりの日" | |
If (intMonth = 5) And (intday = 5) Then strRet = "こどもの日" | |
If (intMonth = 8) And (intday = 11) And (intyear >= 2016) And (intyear <> 2020) And (intyear <> 2021) Then strRet = "山の日" | |
If (intMonth = 11) And (intday = 3) Then strRet = "文化の日" | |
If (intMonth = 11) And (intday = 23) Then strRet = "勤労感謝の日" | |
If (intMonth = 12) And (intday = 23) And (intyear <= 2018) Then strRet = "天皇誕生日" | |
' 春分/秋分 | |
If (2000 <= intyear And intyear <= 2030) Then | |
If (intMonth = 3 And intday = 春分(intyear - 2000)) Then strRet = "春分の日" | |
If (intMonth = 9 And intday = 秋分(intyear - 2000)) Then strRet = "秋分の日" | |
End If | |
' ハッピーマンデー(特定週の月曜日固定) | |
If (intWeekDay = 2) Then ' 月曜 | |
If (intMonth = 1) And (intWeek = 2) Then strRet = "成人の日" | |
If (intMonth = 7) And (intWeek = 3) And (intyear <> 2020) Then strRet = "海の日" | |
If (intMonth = 9) And (intWeek = 3) Then strRet = "敬老の日" | |
If (intMonth = 10) And (intWeek = 2) And (intyear <= 2019) Then strRet = "体育の日" | |
If (intMonth = 10) And (intWeek = 2) And (intyear >= 2022) Then strRet = "スポーツの日" | |
End If | |
' 2019年特例 | |
If (intyear = 2019) Then | |
If (intMonth = 5) And (intday = 1) Then strRet = "天皇の即位の日" | |
If (intMonth = 10) And (intday = 22) Then strRet = "即位礼正殿の儀の行われる日" | |
End If | |
' 2020年特例 | |
If (intyear = 2020) Then | |
If (intMonth = 7) And (intday = 23) Then strRet = "海の日" | |
If (intMonth = 7) And (intday = 24) Then strRet = "スポーツの日" | |
If (intMonth = 8) And (intday = 10) Then strRet = "山の日" | |
End If | |
' 2021年特例 | |
If (intyear = 2021) Then | |
If (intMonth = 7) And (intday = 22) Then strRet = "海の日" | |
If (intMonth = 7) And (intday = 23) Then strRet = "スポーツの日" | |
If (intMonth = 8) And (intday = 8) Then strRet = "山の日" | |
End If | |
日本の祝日 = strRet | |
End Function | |
'------------------------------------------------------------------ | |
Private Function is振替休日(vInDate As Date) As Boolean | |
' 自身が祝日であれば、振替ではない | |
If 日本の祝日(vInDate) <> "" Then | |
is振替休日 = False | |
Exit Function | |
End If | |
' 自身が祝日でなければ、祝日が日曜まで連続しているかを確認 | |
For n = 1 To 7 | |
Dim d As Date | |
d = DateAdd("d", -n, vInDate) ' n日前 | |
' 日曜に至るまでに祝日が途切れれば振替でない | |
If (日本の祝日(d) = "") Then | |
is振替休日 = False | |
Exit Function | |
Else | |
If (Weekday(d) = 1) Then | |
' 祝日であり日曜 | |
is振替休日 = True | |
Exit Function | |
Else | |
' LOOP(さらに前日を確認) | |
End If | |
End If | |
Next | |
End Function | |
'------------------------------------------------------------------ | |
Private Function is国民の休日(vInDate As Date) As Boolean | |
' 自身が祝日/日曜であれば、国民の休日ではない | |
If (日本の祝日(vInDate) <> "") Or (Weekday(vInDate) = 1) Then | |
is国民の休日 = False | |
Exit Function | |
End If | |
If 日本の祝日(DateAdd("d", -1, vInDate)) <> "" And _ | |
日本の祝日(DateAdd("d", 1, vInDate)) <> "" Then | |
is国民の休日 = True | |
Exit Function | |
End If | |
is国民の休日 = False | |
End Function | |
'------------------------------------------------------------------ | |
Private Function 非公式の休日(vInDate As Date) As String | |
Dim strRet As String | |
strRet = "" | |
Dim intday As Integer: intday = Day(vInDate) | |
Dim intMonth As Integer: intMonth = Month(vInDate) | |
' 盂蘭盆を 8/13~15 と仮定した場合 | |
If (intMonth = 8) And (13 <= intday And intday <= 15) Then strRet = "盆" | |
' 群馬県民の日 | |
If (intMonth = 10) And (intday = 28) Then strRet = "群" | |
非公式の休日 = strRet | |
End Function |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
例)使用例 (ワークシートのセルにて) | |
=日本の休み("2015/9/19") | |
→ "土" (土曜日) | |
=日本の休み("2015/9/20") | |
→ "日" (日曜日) | |
=日本の休み("2015/9/21") | |
→ "祝" (祝日) | |
=日本の休み("2015/9/22") | |
→ "休" (国民の休日) | |
=日本の休み("2015/5/6") | |
→ "振" (振替休日) | |
=日本の休み("2015/5/7") | |
→ "" (平日) | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment