VBAで残業時間を自動計算する方法｜TimeValue・深夜残業・月間合計の実用コード - Excelのムダを省くJIMOVE

VBAで残業時間を自動計算するには、退勤時刻から所定終業時刻（例：18:00）を引くだけです。TimeValue で基準時刻を設定し、ループで全行を処理するだけで、毎月の手集計をまるごと自動化できます。深夜時間（22:00以降）を別列に出したり、休憩時間を差し引いたりする応用も、同じ考え方で対応できます。

この記事では、次の内容を順番に解説します。

残業時間を自動計算する基本コードと動作の仕組み
遅刻・早退の有無もあわせてチェックする方法
深夜残業時間を別列に出力する方法
月間残業時間を合計する方法
よくある質問（時刻がシリアル値になる・日をまたぐ退勤時刻の扱い等）

残業時間を自動計算する基本コードを理解するには？

次のような勤怠表を想定します。A列：日付、B列：出勤時刻、C列：退勤時刻、D列：残業時間（出力先）。

Sub CalcOvertime()

    Dim i As Long
    Dim lastRow As Long
    Dim endTime As Date
    Dim overtime As Double

    endTime = TimeValue("18:00")  ' 所定の勤務終了時刻

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow

        ' 退勤時刻が入っていない行はスキップ
        If Not IsDate(Cells(i, 3).Value) Then GoTo Continue

        If Cells(i, 3).Value > endTime Then
            overtime = Cells(i, 3).Value - endTime
        Else
            overtime = 0
        End If

        Cells(i, 4).Value = overtime
        Cells(i, 4).NumberFormatLocal = "[h]:mm"

Continue:
    Next i

    MsgBox "残業時間の計算が完了しました。"

End Sub

このコードのポイントは次の通りです。

TimeValue("18:00") で所定終業時刻を日付型として取得する
退勤時刻が所定時刻より大きい（遅い）場合に差分を残業時間として計算する
[h]:mm の書式は24時間を超えた場合でも正しく表示される（例：25:30）
IsDate で時刻が入っていない行をスキップし、エラーを防ぐ

所定終業時刻を変更したい場合は TimeValue("18:00") の部分だけ修正します。

遅刻・早退の有無もあわせてチェックするには？

出勤時刻も合わせて確認し、遅刻・早退フラグをE列に出力するコードです。

Sub CalcOvertimeWithCheck()

    Dim i As Long
    Dim lastRow As Long
    Dim startTime As Date
    Dim endTime As Date
    Dim memo As String

    startTime = TimeValue("9:00")
    endTime   = TimeValue("18:00")

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow

        If Not IsDate(Cells(i, 2).Value) Or Not IsDate(Cells(i, 3).Value) Then GoTo Continue

        memo = ""

        ' 遅刻チェック（出勤が9:00より後）
        If Cells(i, 2).Value > startTime Then
            memo = memo & "遅刻 "
        End If

        ' 早退チェック（退勤が18:00より前）
        If Cells(i, 3).Value < endTime Then
            memo = memo & "早退 "
        End If

        ' 残業時間の計算
        If Cells(i, 3).Value > endTime Then
            Cells(i, 4).Value = Cells(i, 3).Value - endTime
            Cells(i, 4).NumberFormatLocal = "[h]:mm"
        Else
            Cells(i, 4).Value = 0
        End If

        ' 備考欄にフラグを入力
        Cells(i, 5).Value = Trim(memo)

Continue:
    Next i

    MsgBox "チェックが完了しました。"

End Sub

遅刻・早退の両方が該当する日は「遅刻早退」と両方表示されます。備考列で一目で確認できるため、月次の集計確認がしやすくなります。

深夜残業時間を別列に出力するには？

22:00以降の深夜残業を通常残業と分けて管理したい場合は、条件を追加するだけです。F列に深夜残業時間を出力します。

Sub CalcOvertimeWithNight()

    Dim i As Long
    Dim lastRow As Long
    Dim endTime As Date
    Dim nightTime As Date

    endTime   = TimeValue("18:00")
    nightTime = TimeValue("22:00")

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow

        If Not IsDate(Cells(i, 3).Value) Then GoTo Continue

        ' 通常残業（18:00〜22:00）
        If Cells(i, 3).Value > endTime Then
            If Cells(i, 3).Value <= nightTime Then
                ' 22:00以前に退勤：通常残業のみ
                Cells(i, 4).Value = Cells(i, 3).Value - endTime
                Cells(i, 5).Value = 0
            Else
                ' 22:00以降に退勤：通常残業＋深夜残業に分割
                Cells(i, 4).Value = nightTime - endTime   ' 18:00〜22:00の4時間固定
                Cells(i, 5).Value = Cells(i, 3).Value - nightTime
            End If
        Else
            Cells(i, 4).Value = 0
            Cells(i, 5).Value = 0
        End If

        Cells(i, 4).NumberFormatLocal = "[h]:mm"
        Cells(i, 5).NumberFormatLocal = "[h]:mm"

Continue:
    Next i

    MsgBox "計算が完了しました。"

End Sub

月間残業時間を合計するには？

残業時間の列を合計して月間合計を出したい場合は、ループ終了後に WorksheetFunction.Sum で集計します。

' ループ終了後に月間合計を出力
Dim totalRow As Long
totalRow = lastRow + 1

Cells(totalRow, 1).Value = "月間合計"
Cells(totalRow, 4).Value = WorksheetFunction.Sum(Range(Cells(2, 4), Cells(lastRow, 4)))
Cells(totalRow, 4).NumberFormatLocal = "[h]:mm"

時刻の合計は通常の SUM 関数で計算できますが、表示書式を [h]:mm にしておかないと24時間を超えた場合に正しく表示されないため、必ず設定してください。

まとめ

残業時間の計算は 退勤時刻 − TimeValue("18:00") の引き算で求められる
時刻の表示書式は [h]:mm（24時間超えに対応）を使う
IsDate で空白・未入力セルをスキップしてエラーを防ぐ
深夜残業は 22:00を境に条件分岐 して別列に出力する
月間合計は WorksheetFunction.Sum で集計し、同じ書式を適用する

よくある質問

残業時間がシリアル値（0.03など）で表示される場合は？

セルの表示形式が「標準」や「数値」になっているためです。コードに Cells(i, 4).NumberFormatLocal = "[h]:mm" を追加してください。すでに計算済みのセルに適用したい場合は、対象範囲を選択して「セルの書式設定」→「ユーザー定義」→ [h]:mm と入力しても同じ結果になります。

日をまたいで退勤した場合（例：翌日0:30退勤）の計算は？

Excelの時刻シリアル値は1日を1.0とするため、0:30は「0.020833…」として扱われます。前日の退勤時刻（例：18:00 = 0.75）より小さい値になるため、そのまま引き算すると負の値になります。日をまたぐ場合は退勤時刻に + 1（1日分）を足して補正します。

Dim exitTime As Double
exitTime = Cells(i, 3).Value

' 退勤時刻が出勤時刻より小さい（日またぎ）場合に補正
If exitTime < Cells(i, 2).Value Then
    exitTime = exitTime + 1
End If

休憩時間を差し引いて実労働時間を計算したい場合は？

退勤時刻から出勤時刻を引いた後、休憩時間を TimeValue で引きます。

Dim workTime As Double
Dim breakTime As Date

breakTime = TimeValue("1:00")  ' 休憩1時間

workTime = Cells(i, 3).Value - Cells(i, 2).Value - breakTime

Cells(i, 4).Value = workTime
Cells(i, 4).NumberFormatLocal = "[h]:mm"

所定終業時刻をセルから参照したい場合は？

マクロ内で固定値にする代わりに、シート上の特定セル（例：H1）から読み込むことができます。

Dim endTime As Date
endTime = TimeValue(Range("H1").Value)  ' H1に"18:00"と入力しておく

これにより、終業時刻を変更したい場合にコードを開かずにセルを編集するだけで対応できます。

祝日・休日出勤の行だけ色を変えたい場合は？

祝日一覧を別シートに用意しておき、Match や Find で日付が一致するかチェックする方法があります。簡易的には、A列の曜日を Weekday 関数で取得して土日（1か7）に背景色をつけるだけでも視認性が上がります。

' 土日の行に背景色をつける
If Weekday(Cells(i, 1).Value) = 1 Or Weekday(Cells(i, 1).Value) = 7 Then
    Rows(i).Interior.Color = RGB(255, 242, 204)  ' 薄い黄色
End If