Excelで勤務時間を計算する方法｜休憩・日またぎ・24時間超

Excelの勤務時間は、終了時刻から開始時刻と休憩を引いて計算します。9:00〜18:00で休憩1:00なら、勤務時間は8:00です。日をまたぐ場合は終了日を指定し、合計の表示形式を[h]:mmにすると24時間を超えても表示できます。

休憩・夜勤・入力ミスを同じ表で確かめられる無料Excelを用意しました。開始・終了・休憩は「時:分」で入力します。5件の作例の合計は36時間15分です。

登録不要・数式入りの.xlsxファイルです。完成ファイルを使う方は最初の入力へ、式から作る方は表と数式の作り方へ進んでください。

無料Excelで勤務時間を計算する

「勤務時間」シートには10件分の入力欄があり、最初の5件に説明用のデータを入れています。黄色のA〜E列が入力欄、F〜I列が計算欄です。各行の勤務時間はH列、合計はH4に表示します。

最初に終了時刻を1つ変えてみる

ファイルをコピーして開き、「勤務時間」のC8を18:00から18:30へ変えてください。H8が8:30、H4が36:45になれば、入力と計算結果を確認できています。確認後はC8を18:00へ戻します。

自分の記録で使うときは、A8:E17の内容だけを消してください。行自体は削除せず、F〜I列の数式を残します。開始をB列、終了をC列、終了日をD列、休憩をE列へ入れると、G列に入力の確認結果が出ます。A列の記録名は任意です。

休憩がない日はE列へ0:00と入力します。休日は、その行のA〜E列の入力だけを消してください。F〜I列の数式は残します。記録名だけを残した行や、休憩を入れ忘れた行は「入力不足」になります。修正待ちの行が1つでもあると、H4は「入力を確認」と表示します。

この教材は1人分の時間計算を試す小さな表です。月の日付の自動生成や、勤務記号による人員配置は月間シフト表の無料Excelと作り方で扱っています。

今月のシフトも組みたい方へ

スタッフ20名・勤務パターン10枠で予定を組む場合は、シフト表 Excelテンプレートに月間の入力欄と集計式が用意されています。下は販売中v1.2の設定画面です。

購入後はZIPを展開し、Excelのコピーを使います。「設定」のD7に年、D8に月を入れ、勤務パターンを登録してください。早番の例はD14＝9:00、E14＝18:00、F14＝60で、G14が8.0hです。無料教材の休憩1:00と、販売版の60分を混同しないようにしてください。

販売版で省けるのは、月間の勤務記号欄、パターン別の必要人数、スタッフ別の予定時間・人件費の集計式を組む作業です。シフトの割り当ては手入力で、出退勤の打刻や実績の取込、給与計算を行う商品ではありません。

月間シフトを組むためのExcelテンプレート

シフト表 Excelテンプレート

¥2,200 税込買い切り

お買い物カゴに追加 画面と機能を見る

休憩を引く勤務時間の計算式

ここからは白紙のブックで作る手順です。シート名を「勤務時間」に変え、7行目に次の見出しを入力してください。無料ファイルを使う場合は、数式を入れ直す必要はありません。

B8に9:00、C8に18:00、D8に「当日」、E8に1:00を入力します。B8:C17の表示形式はh:mm、E8:E17とH8:H17は[h]:mmにしてください。H8へ次の式を入れると、8:00になります。

=C8-B8-E8

8時間はExcel内部では1日の3分の1です。結果が0.333333のように見える場合は、H8の表示形式を確認してください。表示を変えても、元の数値は変わりません。

休憩が2回なら、合計した時間をE列へ入れます。45分と15分なら1:00です。この教材では開始・終了・休憩を分単位で入力し、秒を含む入力は確認対象にします。

日をまたぐ勤務は終了日を指定する

22:00〜翌6:00の勤務は、開始B10＝22:00、終了C10＝6:00、終了日D10＝「翌日」、休憩E10＝1:00です。翌日分の1を足してから休憩を引くと、勤務時間は7:00になります。

=C10-B10+IF(D10="翌日",1,0)-E10

D8:D17を選び、PC版Excelの［データ］→［データの入力規則］でリストを設定してください。元の値は当日,翌日です。「翌日」は実際に日付をまたぐ記録で選びます。終了が開始より早いだけでは、夜勤か入力ミスかを判断できません。

MOD関数で計算するときの条件

1勤務が24時間未満で、開始・終了の時刻が正しいと分かっていれば、次の式でも日またぎを計算できます。

=MOD(C10-B10,1)-E10

MODは差を1日で割った余りにするため、9:00と8:00のような入力も23時間として計算します。開始と終了が同じなら差は0になり、24時間や48時間を区別できません。無料教材では終了日を選ぶ方式にし、同時刻や24時間以上の区間は確認メッセージを出します。

同時刻の行が休日なら、その行のA〜E列の入力だけを消してください。F〜I列の数式は残します。24時間以上の区間なら、後述の日時差シートで開始日と終了日も入れます。

合計が24時間を超えるときは表示形式を変える

H8:H17の勤務時間を合計する基本式は次のとおりです。H4に入れ、表示形式を[h]:mmにしてください。

=SUM(H8:H17)

初期5件の合計は36:15です。h:mmのままだと24時間分が表示されず、12:15に見えます。合計の式を変える前に、角括弧付きの[h]になっているか確認してください。

PC版ExcelではH4を選び、［セルの書式設定］の［表示形式］から［ユーザー定義］を開きます。種類に[h]:mmを入力して確定してください。Microsoftの時間の加算・減算の手順でも、24時間以上の表示形式を説明しています。

無料ファイルには表示形式を設定済みで、Excel Webでも合計36:15と日時差25:00を確認しています。デスクトップ版Excelでの書式設定操作は今回未確認です。

後述の入力確認を加えると、合計は確認メッセージが残る間だけ止まります。SUMだけでは文字や空欄を無視して合計するため、入力途中の数字を完成した合計と取り違えないためです。

7時間30分を7.5時間へ換算する

H9の7:30を小数時間で表示するには、24を掛けます。I9へ次の式を入れ、表示形式を「数値」の小数2桁にすると7.50になります。

=H9*24

7.30時間は7時間18分です。30分は1時間の半分なので、7時間30分を小数で書くと7.50になります。小数時間のセルへ時刻の表示形式を付けると別の時刻に見えるため、I列は数値のまま使ってください。

無料教材の休憩欄は時:分で統一しています。休憩30分なら0:30、1時間30分なら1:30です。1や1.5と入力すると1日や1.5日として扱われるので、数値だけで入力しないでください。

入力漏れ・長すぎる休憩を確認してから合計する

無料教材のG列には、次の確認結果が出ます。計算できない行のH列とI列は空欄にし、H4の合計は「入力を確認」で止めます。入力済みの有効な行は、計算結果を残す仕様です。

たとえば、B13に9:00、C13に8:00、D13に「当日」、E13に0:00を入れると「終了日・時刻を確認」です。C13の入力ミスなら正しい時刻に直します。実際に翌日の8:00までだった場合だけ、D13を「翌日」にしてください。

時刻が文字列のままなら、セル先頭のアポストロフィや全角のコロンを確認します。表示形式を「標準」へ変え、半角で9:00と入力し直してください。#####が出る場合は列幅も広げます。負の時間差が原因なら、幅を広げるだけでは直りません。

白紙から作る場合の確認式

以下は無料ファイルに入っている式です。F8、G8、H8、I8の順に入れてください。H8は先ほどの基本式から置き換えます。最後にF8:I8を選び、17行目まで下へコピーします。F列とI列の表示形式は数値の小数2桁、H列は[h]:mmです。

F8は、終了日の指定を加えた経過時間を時間数で求めます。異常な入力では負の値などが出ることがありますが、その行はG列で確認対象になります。

=IF(COUNTA(A8:E8)=0,"",IF(COUNTBLANK(B8:D8)>0,"",IF(COUNT(B8:C8)<>2,"",IF(AND(D8<>"当日",D8<>"翌日"),"",(C8-B8+IF(D8="翌日",1,0))*24))))

G8は必須欄、数値、時刻の範囲、終了日、秒、同時刻、休憩の長さを順に確認します。0:00と空欄を区別するため、空欄の確認にはCOUNTBLANKを使っています。

=IF(COUNTA(A8:E8)=0,"",IF(COUNTBLANK(B8:E8)>0,"入力不足",IF(COUNT(B8:C8,E8)<>3,"時刻・休憩を確認",IF(OR(B8<0,B8>=1,C8<0,C8>=1,E8<0,E8>=1),"時刻・休憩を確認",IF(AND(D8<>"当日",D8<>"翌日"),"終了日を選択",IF(OR(ABS(B8*1440-ROUND(B8*1440,0))>0.0000001,ABS(C8*1440-ROUND(C8*1440,0))>0.0000001,ABS(E8*1440-ROUND(E8*1440,0))>0.0000001),"分単位で入力",IF(B8=C8,"同時刻を確認",IF(F8<=0,"終了日・時刻を確認",IF(F8>=24,"日時入力へ",IF(E8*24>F8+0.0000001,"休憩超過","OK"))))))))))

H8ではG8がOKのときに休憩を引きます。分単位の入力なので、分の差を整数に丸めてから1日分の1440で割ります。休憩と経過時間が等しい場合も、微小な計算誤差を残さず0:00にします。

=IF(G8="OK",ROUND(F8*60-E8*1440,0)/1440,"")

I8は勤務時間を小数時間へ換算します。H8が空欄のときはI8も空欄です。

=IF(ISNUMBER(H8),H8*24,"")

H4のSUMを次の式へ置き換えます。G列の10行から空欄とOKの行を除き、確認が必要な行が残っていないか調べます。全行が未入力なら、合計も空欄です。

=IF(ROWS(G8:G17)-COUNTBLANK(G8:G17)-COUNTIFS(G8:G17,"OK")>0,"入力を確認",IF(COUNT(H8:H17)=0,"",SUM(H8:H17)))

I4に次の式を入れ、数値の小数2桁にすると、合計36:15を36.25時間でも確認できます。

=IF(ISNUMBER(H4),H4*24,"")

確認後は初期の5件でH4＝36:15を確かめ、E8を一度空欄にしてください。G8が「入力不足」、H4が「入力を確認」に変わることを見て、E8を1:00へ戻します。入力規則は貼り付けで回避される場合があるため、G列の確認も残してください。

1回の区間が24時間以上なら日付も入力する

複数日の合計が24時間を超える場合は、H4の表示形式だけで対応できます。一方、1回の開始から終了までが24時間以上なら、開始日と終了日が必要です。無料ファイルの「日時差」シートを使ってください。

27時間の日時差から休憩2時間を引いて25時間です。これは日付を含めた計算方法を確認する例で、長時間勤務を勧めるものではありません。

日時を含むセル同士なら、基本の計算は=C8-B8-D8です。B8:C10はyyyy/mm/dd h:mm、D8:D10とG8:G10は[h]:mmで表示します。無料ファイルでは入力確認を挟み、E列の経過時間から休憩を引いた分数を整数に丸める式をG8に入れています。

=IF(F8="OK",ROUND(E8*60-D8*1440,0)/1440,"")

自分のデータはA8:D10へ入力してください。開始・終了は日付と時刻を1つのセルに入れます。終了日時が過去、日時が同じ、入力漏れ、長すぎる休憩などはF列に表示されます。休憩は24時間未満を入力する教材です。

「勤務時間」と「日時差」は別々に集計します。同じ記録を両方へ入れて足すと二重集計になるため、記録はどちらか一方へ入れてください。給与や法定残業、深夜割増の判定は含めていません。

勤務時間の計算から月間シフト作成へ進む

この無料教材では、入力した時刻の差と休憩から勤務時間を確かめられます。誰がどの日に入るかを決め、人数の不足まで確認したい場合は、月間シフト表の記事へ進んでください。5名分の無料作例で、早番・遅番・休みを選ぶ手順を説明しています。

販売中のシフト表 Excelテンプレートは、20名・10勤務パターンの月間予定管理用です。「スタッフ」のB7:F26に氏名・雇用形態・給与形態・賃金・上限時間を登録し、「シフト表」のD8:AH27へ勤務記号を入れます。

無料教材で行うのは時刻ごとの計算です。販売版では勤務パターンの時間を一度設定し、各日の記号を選びます。パターン別の必要人数と不足表示、スタッフ別の出勤日数・予定時間・人件費見込みの集計式を作る準備を省けます。

翌月は別名でコピーを保存し、対象年・月と勤務欄を更新してください。実績時刻の記録や打刻、残業・深夜割増・交通費・社会保険料を含む給与計算には対応していません。PCでの利用をおすすめします。

20名分の月間シフトを設定済みの表で組む

シフト表 Excelテンプレート

¥2,200 税込買い切り

お買い物カゴに追加 画面と機能を見る

買い切り・テンプレートの月額料金なし
お支払い完了後、Excel＋説明テキストのZIPをダウンロード

カートの商品で購入手続きへ →

無料教材の確認範囲

2026年10月20日にExcel Webで、初期合計36:15、終了時刻を30分延ばしたときの合計36:45、休憩未入力の警告、勤務時間0:00、夜勤7:00、日時差25:00を確認しました。試験入力は元に戻しています。デスクトップ版Excel、PDF出力、紙への印刷は未確認です。本文の表画像は教材ファイルを描画したもので、Excelアプリの画面キャプチャではありません。

販売中v1.2のコピーもExcel Webで確認しました。作例の出勤21日・予定168時間が、早番1日を休日へ変えると20日・160時間に更新され、元に戻すと21日・168時間に戻りました。