【人材】シフト表から実働時間と深夜時間（22時〜5時）を計算する（日またぎ勤務に対応）
出勤・退勤・休憩のシフト表から、日またぎの夜勤も含めて1日ごとの実働時間と深夜時間（22時〜翌5時）を計算し、人別に合計するマクロです。時刻を分に直して重なりを求める考え方を解説します。
想定する場面
人材派遣や警備、物流、介護などの現場では、夜勤や日をまたぐシフトが珍しくありません。給与計算の前にシフト実績から実働時間と深夜時間を確認したいとき、日またぎの勤務を手計算するのは手間がかかり、22時や翌5時をまたぐ勤務で深夜時間を数え間違えることもよくあります。
このマクロは、シフト表の各行について実働時間と深夜時間を計算して書き込み、社員ごとに勤務日数・実働時間・深夜時間の合計を出します。
データの形（例）と計算の考え方
「シフト」シートを次の列構成とします（説明用の架空の構成です）。出勤・退勤は時刻だけが入っており、退勤が出勤より前の時刻なら翌日の退勤（日またぎ）とみなします。
計算は、出勤日の0時からの経過分数に直して行います。たとえば 21:00 出勤・翌6:00 退勤なら、出勤 1260分、退勤 360＋1440＝1800分です。深夜の時間帯は、出勤日の 0〜300分（0時〜5時）、1320〜1740分（22時〜翌5時）、2760分以降（翌日22時以降）の3つで、勤務時間とそれぞれの時間帯が重なる長さを合計すれば深夜時間になります。
- A列：勤務日
- B列：社員番号
- C列：氏名
- D列：出勤時刻
- E列：退勤時刻
- F列：休憩（分）
- G列：実働時間、H列：深夜時間（マクロが書き込む）
コード
時刻は Excel では1日を1とする小数なので、1440 を掛けて分に直します。時刻の書式のセルを .Value で読むと Date 型になり、IsNumeric が False を返してしまうため、Value2 で小数のまま読み込んでいます。小数の誤差で 59.9999分のようにならないよう、Round で整数の分にそろえています。結果は「分 ÷ 1440」で時刻の値に戻し、24時間を超えても表示できる [h]:mm の形式で表示します。
Sub CalcShiftHours()
Dim ws As Worksheet, outWs As Worksheet, data As Variant, res() As Variant
Dim dic As Object, k As Variant, rec As Variant, out() As Variant
Dim lastRow As Long, i As Long, n As Long
Dim sMin As Long, eMin As Long, workMin As Long, nightMin As Long
Set ws = ThisWorkbook.Worksheets("シフト")
Set outWs = ThisWorkbook.Worksheets("個人別集計")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then MsgBox "データがありません": Exit Sub
data = ws.Range("A2:F" & lastRow).Value2 ' 時刻を Date 型ではなく小数で受け取る
ReDim res(1 To UBound(data, 1), 1 To 2)
Set dic = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(data, 1)
If IsNumeric(data(i, 4)) And IsNumeric(data(i, 5)) And _
Not IsEmpty(data(i, 4)) And Not IsEmpty(data(i, 5)) Then
sMin = ToMinutes(data(i, 4))
eMin = ToMinutes(data(i, 5))
If eMin <= sMin Then eMin = eMin + 1440 ' 日またぎ勤務
workMin = eMin - sMin - Val(CStr(data(i, 6)))
If workMin < 0 Then workMin = 0
nightMin = Overlap(sMin, eMin, 0, 300) _
+ Overlap(sMin, eMin, 1320, 1740) _
+ Overlap(sMin, eMin, 2760, 3180)
res(i, 1) = workMin / 1440
res(i, 2) = nightMin / 1440
k = CStr(data(i, 2))
If dic.Exists(k) Then rec = dic(k) Else rec = Array(data(i, 3), 0&, 0&, 0&)
rec(1) = rec(1) + 1
rec(2) = rec(2) + workMin
rec(3) = rec(3) + nightMin
dic(k) = rec
Else
res(i, 1) = "": res(i, 2) = "" ' 休み・未入力
End If
Next i
ws.Range("G1:H1").Value = Array("実働時間", "深夜時間")
ws.Range("G2:H" & lastRow).Value = res
ws.Range("G2:H" & lastRow).NumberFormatLocal = "[h]:mm"
outWs.Cells.Clear
outWs.Range("A1:E1").Value = Array("社員番号", "氏名", "勤務日数", "実働時間計", "深夜時間計")
If dic.Count = 0 Then Exit Sub
ReDim out(1 To dic.Count, 1 To 5)
For Each k In dic.Keys
rec = dic(k)
n = n + 1
out(n, 1) = k: out(n, 2) = rec(0): out(n, 3) = rec(1)
out(n, 4) = rec(2) / 1440: out(n, 5) = rec(3) / 1440
Next k
outWs.Range("A2").Resize(n, 5).Value = out
outWs.Range("D2:E" & n + 1).NumberFormatLocal = "[h]:mm"
MsgBox n & " 人分を集計しました"
End Sub
' 時刻の値を0時からの分に直す（日付部分があれば取り除く）
Private Function ToMinutes(ByVal v As Variant) As Long
Dim d As Double
d = CDbl(v)
ToMinutes = CLng(Round((d - Int(d)) * 1440, 0)) Mod 1440
End Function
' 2つの区間が重なる長さ（分）
Private Function Overlap(ByVal s1 As Long, ByVal e1 As Long, _
ByVal s2 As Long, ByVal e2 As Long) As Long
Dim a As Long, b As Long
If s1 > s2 Then a = s1 Else a = s2
If e1 < e2 Then b = e1 Else b = e2
If b > a Then Overlap = b - a
End Functionコードのポイント
深夜時間を「22時以降なら」「5時より前なら」と条件分岐で数えようとすると、21時出勤・翌6時退勤のような勤務で場合分けが増え、間違いやすくなります。この記事のように時間帯を区間として扱い、勤務の区間との重なりを足し合わせる方法なら、早朝出勤（4時出勤など）も夜勤も同じ式で計算できます。
合計は分の整数で足し込み、最後に 1440 で割って時刻の値に戻しています。時刻の小数のまま足し込むより誤差が出にくく、表示形式を [h]:mm にすることで、月の合計が24時間を超えても正しく表示されます。数値の時間（8.5 など）で出したい場合は、1440 ではなく 60 で割ります。
注意点
このサンプルでは、休憩時間は実働時間からだけ差し引き、深夜時間からは差し引いていません。深夜帯に休憩を取る勤務では深夜時間が多めに出るため、正確に計算するには休憩の開始・終了時刻を記録し、休憩の区間と深夜の区間の重なりを差し引く処理を加えてください。
- 出勤と退勤が同じ時刻の行は、24時間勤務とみなされる（入力ミスがないか確認する）
- 24時間を超える連続勤務は、このサンプルの前提（翌日の退勤まで）では扱えない
- 深夜時間の割増や休憩の扱いは、労働基準法と各社の就業規則に従って最終確認する
※列構成は説明用の架空の例です。給与計算に使う場合は、勤怠システムの集計結果と突き合わせて確認してください。