EXCELで日数計算

はじめに

EXCEL関数で稼働日数を計算するものが有りますが、シート1枚でパッと見れたら良いよなぁと思いまして作ってみました。

ダウンロード

利用したいだけの方は、こちらからダウンロードしてご利用ください。

※ZIP圧縮されておりますので、適当なフォルダに解凍してください。

日数計算

1 ファイル 58.28 KB

ダウンロード

ちょこっと解説

祝日については、設定シートからダウンロードできるようになっております。

説明については、CSVファイルをダウンロードしてシートにセットするを参照ください。

Sheet1

A1セルに年を入力するとカレンダーが更新されます。土日祝日は条件付き書式で色づけされるようにしております。

「祝日セット」ボタンを押すと祝日と休日がセットされます。

右側に曜日別の集計など表示されます。

一番右側の平日と休日セルは、土日祝日が休日とした場合の日数になります。

VBA

Option Explicit

''' 祝日セット
Private Sub cmdSetHoliday_Click()
    Dim nen As Integer
    Dim tuki As Integer
    Dim holiday() As String
    Dim userHoliday() As String
    Dim i As Integer
    Dim d As Integer

    Application.ScreenUpdating = False
    
    nen = Me.Range("A1").Value
    
    For tuki = 1 To 12
        ' 祝日取得
        holiday = GetHoliday(nen, tuki)
        ' 休日取得
        userHoliday = GetUserHoliday(tuki)
        
        For i = 3 To 33
            d = Me.Cells(2, i)
            Me.Cells(tuki * 2 + 2, i).Value = ""
            If holiday(d) <> "" Then
                Me.Cells(tuki * 2 + 2, i).Value = "祝"
            End If
            If userHoliday(d) <> "" Then
                Me.Cells(tuki * 2 + 2, i).Value = "祝"
            End If
        Next
    Next tuki

    Application.ScreenUpdating = True
    
    MsgBox "祝日セット完了しました。", vbInformation
End Sub


''' 祝日取得
Private Function GetHoliday(y As Integer, m As Integer)
On Error GoTo Err_Handler
    
    Dim i As Integer
    Dim d(31) As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim xl_file As String
    Dim sql As String
    Dim curRow As Integer
    
    Dim sYMD As Date
    Dim eYMD As Date
    
    'ツールメニューの参照設定'
    ' Microsoft ActiveX Data Objects 2.8 Library'
    'チェック'
    xl_file = ThisWorkbook.FullName '他のブックを指定しても良い'
 
    Set cn = New ADODB.Connection
    cn.Provider = "MSDASQL"
    #If Win64 Then
        ' 64bit
        cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & "DBQ=" & xl_file & "; ReadOnly=False;"
    #Else
        ' 32bit
        cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & "DBQ=" & xl_file & "; ReadOnly=False;"
    #End If
    cn.Open
    
    Set rs = New ADODB.Recordset
    
    sYMD = CDate(y & "/" & m & "/1")
    eYMD = DateAdd("d", -1, DateAdd("m", 1, sYMD))
    
    sql = "SELECT 国民の祝日・休日月日, 国民の祝日・休日名称 FROM [祝日$]" _
        & "  WHERE" _
        & "  国民の祝日・休日月日 >= #" & sYMD & "# AND 国民の祝日・休日月日 <= #" & eYMD & "#"
    rs.Open sql, cn, adOpenStatic
    
    ' 祝日配列初期化
    For i = 1 To 31
        d(i) = ""
    Next i
    Do While Not rs.EOF
        d(Format(rs.Fields("国民の祝日・休日月日"), "d")) = rs.Fields("国民の祝日・休日名称")
        rs.MoveNext
    Loop
    
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
    
    GetHoliday = d
    Exit Function
    
Err_Handler:
    MsgBox "祝日取得 : " & Err.Description, vbExclamation
    On Error Resume Next
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

End Function

''' ユーザ休日取得
Private Function GetUserHoliday(m As Integer)
On Error GoTo Err_Handler
    
    Dim i As Integer
    Dim d(31) As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim xl_file As String
    Dim sql As String
    Dim curRow As Integer
    
    Dim sYMD As Date
    Dim eYMD As Date
    
    'ツールメニューの参照設定'
    ' Microsoft ActiveX Data Objects 2.8 Library'
    'チェック'
    xl_file = ThisWorkbook.FullName '他のブックを指定しても良い'
 
    Set cn = New ADODB.Connection
    cn.Provider = "MSDASQL"
    #If Win64 Then
        ' 64bit
        cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & "DBQ=" & xl_file & "; ReadOnly=False;"
    #Else
        ' 32bit
        cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & "DBQ=" & xl_file & "; ReadOnly=False;"
    #End If
    cn.Open
    
    Set rs = New ADODB.Recordset
    
    sql = "SELECT 日, 休日名 FROM [休日$]" _
        & "  WHERE" _
        & "  月 = " & m
    rs.Open sql, cn, adOpenStatic
    
    ' 祝日配列初期化
    For i = 1 To 31
        d(i) = ""
    Next i
    Do While Not rs.EOF
        d(rs!日) = rs!休日名
        rs.MoveNext
    Loop
    
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
    
    GetUserHoliday = d
    Exit Function
    
Err_Handler:
    MsgBox "休日取得 : " & Err.Description, vbExclamation
    On Error Resume Next
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

End Function

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

Option Explicit

''' 祝日セット

Private Sub cmdSetHoliday_Click()

Dim nen As Integer

Dim tuki As Integer

Dim holiday() As String

Dim userHoliday() As String

Dim i As Integer

Dim d As Integer

Application.ScreenUpdating = False

nen = Me.Range("A1").Value

For tuki = 1 To 12

' 祝日取得

holiday = GetHoliday(nen, tuki)

' 休日取得

userHoliday = GetUserHoliday(tuki)

For i = 3 To 33

d = Me.Cells(2, i)

Me.Cells(tuki * 2 + 2, i).Value = ""

If holiday(d) <> "" Then

Me.Cells(tuki * 2 + 2, i).Value = "祝"

End If

If userHoliday(d) <> "" Then

Me.Cells(tuki * 2 + 2, i).Value = "祝"

End If

Next tuki

Application.ScreenUpdating = True

MsgBox "祝日セット完了しました。", vbInformation

End Sub

''' 祝日取得

Private Function GetHoliday(y As Integer, m As Integer)

On Error GoTo Err_Handler

Dim i As Integer

Dim d(31) As String

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim xl_file As String

Dim sql As String

Dim curRow As Integer

Dim sYMD As Date

Dim eYMD As Date

'ツールメニューの参照設定'

' Microsoft ActiveX Data Objects 2.8 Library'

'チェック'

xl_file = ThisWorkbook.FullName '他のブックを指定しても良い'

Set cn = New ADODB.Connection

cn.Provider = "MSDASQL"

#If Win64 Then

' 64bit

cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & "DBQ=" & xl_file & "; ReadOnly=False;"

#Else

' 32bit

cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & "DBQ=" & xl_file & "; ReadOnly=False;"

#End If

cn.Open

Set rs = New ADODB.Recordset

sYMD = CDate(y & "/" & m & "/1")

eYMD = DateAdd("d", -1, DateAdd("m", 1, sYMD))

sql = "SELECT 国民の祝日・休日月日, 国民の祝日・休日名称 FROM [祝日$]" _

& " WHERE" _

& " 国民の祝日・休日月日 >= #" & sYMD & "# AND 国民の祝日・休日月日 <= #" & eYMD & "#"

rs.Open sql, cn, adOpenStatic

' 祝日配列初期化

For i = 1 To 31

d(i) = ""

Next i

Do While Not rs.EOF

d(Format(rs.Fields("国民の祝日・休日月日"), "d")) = rs.Fields("国民の祝日・休日名称")

rs.MoveNext

Loop

rs.Close

cn.Close

Set rs = Nothing

Set cn = Nothing

GetHoliday = d

Exit Function

Err_Handler:

MsgBox "祝日取得 : " & Err.Description, vbExclamation

On Error Resume Next

rs.Close

cn.Close

Set rs = Nothing

Set cn = Nothing

End Function

''' ユーザ休日取得

Private Function GetUserHoliday(m As Integer)

On Error GoTo Err_Handler

Dim i As Integer

Dim d(31) As String

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim xl_file As String

Dim sql As String

Dim curRow As Integer

Dim sYMD As Date

Dim eYMD As Date

'ツールメニューの参照設定'

' Microsoft ActiveX Data Objects 2.8 Library'

'チェック'

xl_file = ThisWorkbook.FullName '他のブックを指定しても良い'

Set cn = New ADODB.Connection

cn.Provider = "MSDASQL"

#If Win64 Then

' 64bit

cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & "DBQ=" & xl_file & "; ReadOnly=False;"

#Else

' 32bit

cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & "DBQ=" & xl_file & "; ReadOnly=False;"

#End If

cn.Open

Set rs = New ADODB.Recordset

sql = "SELECT 日, 休日名 FROM [休日$]" _

& " WHERE" _

& " 月 = " & m

rs.Open sql, cn, adOpenStatic

' 祝日配列初期化

For i = 1 To 31

d(i) = ""

Next i

Do While Not rs.EOF

d(rs!日) = rs!休日名

rs.MoveNext

Loop

rs.Close

cn.Close

Set rs = Nothing

Set cn = Nothing

GetUserHoliday = d

Exit Function

Err_Handler:

MsgBox "休日取得 : " & Err.Description, vbExclamation

On Error Resume Next

rs.Close

cn.Close

Set rs = Nothing

Set cn = Nothing

End Function

「祝日セット」ボタンを押すと、cmdSetHoliday_Click 部分が実行されます。

A1セルの年を取得して月の繰り返し（12回）で月を取得し日の繰り返し（31回）で”祝”文字を曜日の下に表示するようにします。

祝日、休日の取得は、SQLを利用しております。

詳しくは、EXCEL VBAでSQL文を使って集計するも参考にしてください。

祝日の取得は、該当する月の1日から末日までの範囲取得するSELECT文となっております。

休日の取得は、該当する月のデータを取得するSELECT文となっております。

最後に

特別難しいことをしておりませんが、いろいろなことを組み合わせておりますので参考にしていただけたら良いかと思います。

EXCELでSQL
条件付き書式で曜日に色設定
COUNTIFで曜日カウント
祝日をダウンロードしてシートにセットなど