エクセルでタイムカードを計算する数式まとめ｜勤務時間・残業・深夜・丸めとエラー対処

Excelでタイムカードを集計していると、「出勤・退勤・休憩から実働を出したい」「8時間超を残業にしたい」「0:30ちょうどを丸めたら0:00になる」「22時〜5時だけ抜き出したい」「マイナス時間で####になる」といったつまずきが次々と出てきます。

この記事では、実働の基本計算から60進の変換、残業・月合計、丸めの誤差、深夜時間、####などのエラー対処まで、コピペできる数式で一気通貫に整理します。15分刻みの丸めの詳細はExcelで15分刻みに丸める、丸めの適法性は刻み・丸めのルールを参照してください。

目次

• タイムカードの基本の勤務時間を計算する数式
• 時間を数値・分に変換する（60進法と電卓の考え方）
• 残業時間を計算して月合計を出す
• 15分・30分単位で丸める数式と0:30が0:00になる対処
• 深夜（22時〜5時）の時間を分けて計算する
• エクセルのマイナス時間・表示のトラブル対処
• 入力するだけで自動計算にするコツ
• まとめ：実働→変換→残業→丸め→深夜の順で数式を組む

タイムカードの基本の勤務時間を計算する数式

まずは、出勤・退勤・休憩から実働時間を出す基本です。B列=出勤、C列=退勤、D列=休憩とします。

出勤・退勤・休憩から実働を出す

同日の勤務（日をまたがない）の基本式です。

=C2-B2-D2

休憩を「1時間」として時刻で持っている場合も同じ形です。

=C2-B2-"1:00"

セルの表示形式は [h]:mm（25時間超も表示）にすると、8時間超の実働も正しく見えます。

日をまたぐ夜勤はMODで計算する

22:00出勤・翌5:30退勤など、日をまたぐ場合は MOD を使います。

=MOD(C2-B2,1)-D2

MOD(退勤-出勤,1) により、退勤が翌日でも1日以内の差として計算できます。休憩は時刻または時間で引きます。

未入力の日を空白にする（空白で13:00になる対処）

出勤・退勤のどちらかが空欄だと、Excelが0として計算し、意図しない値（13:00など）が出ることがあります。

=IF(OR(B2="",C2=""),"",MOD(C2-B2,1)-D2)

未入力の日は空白、入力済みの日だけ実働を計算します。

時間を数値・分に変換する（60進法と電卓の考え方）

給与計算や残業の丸めでは、時刻（60進）を数値（10進）や分に直す必要がよく出ます。

8:30を8.5にする（×24）

実働セル（時刻形式）を時間の数値に変換します。

=E2*24

8:30（実働）→ 8.5（8時間30分＝8.5時間）

分に直す・0.25刻みの数値にする

分に変換する場合は *1440（1日=1440分）です。

=E2*1440

0.25（15分）刻みの数値にする例です。

=MROUND(E2*24, 0.25)

電卓で計算するときの60進法の注意

電卓では「時間は60進」です。8時間30分は 8 + 30÷60 = 8.5 時間になります。Excelでは実働セルに *24 をかけると同じ変換が自動で行われます。電卓で 8.30 と入力すると8時間30分ではなく8.3時間になるため、混同に注意してください。

残業時間を計算して月合計を出す

実働が出たら、定時超過や8時間超を残業として計算します。

8時間超・定時超を残業として出す

所定労働時間が8時間の場合です（E2=実働）。

=MAX(E2-"8:00",0)

定時が17:30で、退勤時刻ベースで残業を出す例です（C2=退勤）。

=MAX(C2-"17:30",0)

残業がない日を空白にする

残業0の日は空白にしたい場合です。

=IF(E2>"8:00",E2-"8:00","")

1か月の残業・合計時間を集計する（[h]:mm表示）

月の残業合計は、残業列を SUM します。

=SUM(F2:F31)

合計セルも表示形式を [h]:mm にしてください。通常の h:mm だと24時間を超えると表示が崩れます。実働の月合計も同様です。

=SUM(E2:E31)

15分・30分単位で丸める数式と0:30が0:00になる対処

丸めは FLOOR・CEILING・MROUND を使います。15分刻みの詳細はExcelで15分刻みに丸めるを参照してください。

FLOOR・CEILING・MROUNDの丸め

実働セル（E2）を30分単位で切り捨てする例です。

=FLOOR(E2,"0:30")

15分切り上げなら CEILING(E2,"0:15")、30分四捨五入なら MROUND(E2,"0:30") です。

0:30ちょうどが0:00になる原因（浮動小数点誤差）と直し方

FLOOR で30分刻みにすると、0:30ちょうどだけ0:00になる——これは浮動小数点の誤差が原因です。対策は次のとおりです。

分整数化してから丸める方法です。

=FLOOR(E2*1440,30)/1440

わずかな誤差を吸収する方法です。

=FLOOR(E2+"0:00:05","0:30")

30分以上は繰り上げ・未満は切り捨てを1式で

「分の端数が30分以上なら1時間に繰り上げ、未満なら切り捨て」というルールの例です。

=IF(MOD(E2*1440,60)>=30,CEILING(E2,"1:00"),FLOOR(E2,"1:00"))

就業規則に合わせて数値（30分など）を調整してください。

深夜（22時〜5時）の時間を分けて計算する

深夜帯（原則22:00〜翌5:00）だけを抜き出すには、出勤・退勤と深夜帯の重なりを計算します。

深夜帯との重なりだけ抽出する数式

B2=出勤、C2=退勤とします。翌5:00を 29:00（24+5）として扱う設計例です。

=MAX(MIN(C2,"29:00")-MAX(B2,"22:00"),0)

日をまたぐ夜勤では、出勤・退勤を「経過時刻」で持つ（22:00を0、翌5:00を7:00など）と安定します。実装はシート設計次第です。

日をまたぐ深夜・深夜残業の考え方

深夜時間と法定外残業は別概念です。深夜は22:00〜5:00の重なり、残業は所定時間超過や8時間超——両方の条件を満たす部分が「深夜残業」になります。まず実働・残業・深夜を別列で出し、必要なら重なりを計算する形が分かりやすいです。

エクセルのマイナス時間・表示のトラブル対処

マイナス時間で####になるときの直し方

退勤が出勤より早い、休憩が長すぎるなどで実働がマイナスになると、セルが #### と表示されることがあります。

対処法は次のとおりです。

1. データの入力ミスを確認する（退勤・休憩の誤り）
2. 表示形式を [h]:mm にし、負の時間を許容する（Excelのバージョン・設定による）
3. 絶対値で表示し、符号を別列で判定する

=IF(E2<0,"要確認",E2)

24時間を超える合計の表示（[h]:mm）

月合計が24時間を超えると、通常の h:mm 表示では正しく見えません。合計セルは [h]:mm にしてください。32:30のように表示されます。

時刻の00表示・0埋め（00数字）

• 9:05と表示：h:mm
• 09:05と常に2桁：hh:mm
• 数値の0埋め：=TEXT(値,"00") や書式 00

深夜を25:00のように表示する場合も [h]:mm を使います。

入力するだけで自動計算にするコツ

IFERRORと空白処理でエラーを消す

計算式を IFERROR で包むと、エラー時に空白や0を返せます。

=IFERROR(MOD(C2-B2,1)-D2,"")

未入力チェックと組み合わせると、より安定します。

=IF(OR(B2="",C2=""),"",IFERROR(MOD(C2-B2,1)-D2,""))

時給区分・土日祝で分けて集計する（発展）

早出・通常・残業、土日祝で分ける場合は、区分列を作り SUMIF / SUMIFS で集計します。

=SUMIF(区分列,"残業",残業時間列)

=SUMIFS(実働列,区分列,"通常",曜日列,"土")

ルールが複雑な場合は、まず実働・残業の基本列を正しく出してから、区分ごとに分けるとデバッグしやすくなります。

まとめ：実働→変換→残業→丸め→深夜の順で数式を組む

• 実働：同日は 退勤-出勤-休憩、日またぎは MOD(退勤-出勤,1)-休憩。未入力は IF(OR(...),"",...) で空白に。
• 変換：時間数値は *24、分は *1440。電卓は60進（分÷60）に注意。
• 残業：MAX(実働-"8:00",0) など。月合計は SUM ＋ 表示 [h]:mm。
• 丸め：FLOOR/CEILING/MROUND。0:30が0:00になるときは FLOOR(値*1440,30)/1440 で誤差を回避。
• 深夜：22:00〜翌5:00との重なりを MAX(MIN(...)-MAX(...),0) で抽出。
• エラー：#### はマイナス時間か列幅。IFERROR と未入力チェックで安定化。
• 丸めの適法性は刻み・丸めのルールを参照。実打刻は残し、不利な一方切り捨てだけにしない。

1行で正しく計算できたら、列をコピーして月全体に広げ、最後に月合計と表示形式を確認してください。