Excelで勤務時間や作業時間を合計すると、合計が24時間を超えた途端に表示が戻ってしまい、正しい時間が分からなくなることがあります。
また、開始時刻と終了時刻から時間を求めたい場合や、分単位で集計したい場合も、セルの表示形式と数式を正しく使い分けることが大切です。
この記事では、24時間以上の時間を正しく合計する表示形式、日をまたぐ勤務の計算、分単位への変換までを、実際の表を使いながら詳しく解説します。
時間集計で押さえたいポイントです。
・開始時刻と終了時刻の差は終了時刻－開始時刻で求めます。
・24時間を超える合計には［h］の表示形式を使います。
・分で集計するときは時間に1440を掛けます。
入力する時刻と計算結果の表示形式を整えれば、勤怠表、工数表、作業記録にも応用できます。
エクセルで時間を合計する基本操作
それではまず、Excelで複数の時間を合計して24時間以上も正しく表示する方法について解説していきます。
| A | B | C |
|---|---|---|
| 日付 | 作業時間 | 備考 |
| 4月1日 | 08:30 | 通常業務 |
| 4月2日 | 07:45 | 会議あり |
| 4月3日 | 09:15 | 通常業務 |
SUM関数による時間の合計
時間を合計するセルには、通常の数値集計と同じようにSUM関数を入力します。
たとえば、1行目を見出しとして、B2からB4に各日の作業時間が入っている場合、B5に次の数式を入力します。
=SUM(B2:B4)
この数式で、B2からB4までの時刻データをまとめて加算できます。
時間は見た目には08:30のように表示されますが、Excel内部では1日を1として扱う小数値です。
そのため、時刻の加算も数値の加算と同じ原理で処理されています。
セルに数式を入力したあと、結果が01:30のように見える場合は、計算ミスではなく表示形式が原因かもしれません。
合計セルでは数式だけでなく、時間の表示形式も必ず確認することが重要です。
24時間を超える合計の表示形式
8時間30分、7時間45分、9時間15分を合計すると25時間30分になります。
しかし、合計セルの表示形式が通常の時刻であるh:mmのままだと、Excelは24時間ごとに表示を繰り返すため、01:30と表示します。
正しく25:30と表示するには、合計セルを選択してセルの書式設定を開き、表示形式のユーザー定義で［h］:mmを指定します。
24時間以上を表示する書式です。
[h]:mm
角かっこ付きのhは、経過時間として時間数を積み上げる指定です。
通常のhは時計の時刻を意味しますが、［h］は25時間、48時間、100時間のような累積時間を表示できます。
勤務時間や残業時間の合計では、最初から［h］:mmを設定しておくと安心です。
オートSUMと入力時の注意点
合計セルを選択してホームタブのオートSUMをクリックすると、近くにある時間データの範囲を自動で候補として選べます。
候補範囲がB2からB4になっていることを確認してEnterキーを押せば、SUM関数が確定します。
時刻を入力するときは、8時30分なら8:30、7時間45分なら7:45のように、時と分の間を半角コロンで区切ります。
8.5のような小数を入力すると、Excelは8時間30分ではなく8.5日など別の数値として扱う可能性があります。
入力セルにもh:mmまたは［h］:mmの書式を設定しておくと、データの意味を見分けやすくなります。
【操作のポイント】合計だけでなく、元になる時間セルも時刻形式で統一します。文字列として入力された時刻はSUM関数で正しく集計できません。
開始時刻と終了時刻から勤務時間を求める方法
続いては、開始時刻と終了時刻を入力して、1日の勤務時間を計算する方法を確認していきます。
| A | B | C | D |
|---|---|---|---|
| 日付 | 開始時刻 | 終了時刻 | 勤務時間 |
| 4月1日 | 09:00 | 18:00 | 09:00 |
| 4月2日 | 08:45 | 17:30 | 08:45 |
終了時刻から開始時刻を引く数式
1行目を見出しとし、B列を開始時刻、C列を終了時刻、D列を勤務時間にする場合、D2には次の数式を入力します。
=C2-B2
終了時刻18:00から開始時刻09:00を引くため、結果は09:00になります。
この結果セルの表示形式はh:mmで構いませんが、月間集計につなげるなら［h］:mmにしておくと設定をそろえられます。
数式をD2に作成したら、セル右下のフィルハンドルを下方向へドラッグします。
各行の参照先が自動で変わり、D3には=C3-B3という数式が入力されます。
開始時刻と終了時刻が同じ日付内にある場合は、引き算だけで勤務時間を算出できます。
休憩時間を差し引く計算
実働時間を求める場合は、開始時刻と終了時刻の差から休憩時間も引きます。
たとえば、E列に休憩時間を入力し、D列に実働時間を表示する構成なら、D2の数式は次の形です。
=C2-B2-E2
09:00から18:00までの9時間に対し、E2に01:00と入力すれば、D2の結果は08:00になります。
休憩時間を60と入力すると60時間や数値60として扱われるため、01:00または0:60ではなく1:00と入力しましょう。
休憩時間が毎日同じなら、別セルに固定の休憩時間を置き、絶対参照を使う方法も便利です。
たとえばF1に01:00を入力した場合は、=C2-B2-$F$1と記述できます。
ドル記号を付けた絶対参照は、数式をコピーしてもF1を固定できる仕組みです。
日をまたぐ勤務時間の計算
夜勤のように開始時刻が22:00、終了時刻が翌日の05:00となる場合、単純な=C2-B2では負の値になります。
このケースでは、MOD関数を使うと日付の境目をまたいでも正しい経過時間を求められます。
=MOD(C2-B2,1)
MOD関数の2つ目の引数である1は1日を表します。
計算結果が負になった場合に1日分を加えるため、22:00から05:00なら07:00が返されます。
終了時刻と開始時刻の両方に日付を入力できる表なら、日時の差として単純に引き算する方法でも計算できます。
ただし、時刻だけを管理する勤務表ではMOD関数の方が入力の負担を抑えられるでしょう。
【操作のポイント】日またぎの行だけ特別な式にすると管理しにくくなります。夜勤が混在する表では、最初からMOD関数を使うと安定します。
分単位へ換算して集計する数式
続いては、時間を分単位に変換し、給与計算や作業工数の集計に使う方法を確認していきます。
| A | B | C |
|---|---|---|
| 日付 | 作業時間 | 分換算 |
| 4月1日 | 08:30 | 510 |
| 4月2日 | 07:45 | 465 |
時間を分へ変換する計算式
Excelでは1日が1として保存されるため、1時間は1日の24分の1、1分は1日の1440分の1です。
したがって、B2の時間を分に変換するには、C2に次の数式を入力します。
=B2*1440
08:30は8時間30分、つまり510分なので、結果は510になります。
結果セルの表示形式は標準または数値に設定します。
時刻形式のままでは、510という分数値ではなく別の時刻表示になるため注意が必要です。
端数が小数で表示される場合は、ROUND関数で四捨五入することもできます。
分単位の勤怠集計では、時間形式のセルを直接合計するより、分にそろえて集計すると確認しやすい場面があります。
分から時間と分の表示へ戻す方法
反対に、510分のような数値を08:30として表示したい場合は、分数を1440で割ります。
たとえば、B2に510が入っている場合、C2には次の数式を入力します。
=B2/1440
数式を入力した後、C2の表示形式を［h］:mmに設定すると08:30と表示されます。
集計結果が1500分のように24時間を超える場合にも、［h］:mmを使えば25:00のように経過時間を確認できます。
給与ソフトへ分数で渡す場合と、社内資料で時間表示したい場合の両方に対応できる考え方です。
数値を時刻へ戻すときは、60ではなく1440で割る点がExcel特有の重要なポイントです。
分単位の集計画面とオートフィル
数式を最初の行に入力したら、フィルハンドルで下の行までコピーします。
上の画面ではC2が赤枠で選択されており、数式バーには=B2*1440が表示されています。
選択セル右下の小さな四角を下へドラッグすると、C3以降にも参照先を変えた数式がコピーされます。
作業時間が空欄の行まで数式をコピーする場合、空欄を0分と表示したくないこともあります。
その場合は、=IF(B2=””,””,B2*1440)のようにIF関数を組み合わせると、入力がない行を空欄のままにできます。
【操作のポイント】分換算の列は数値形式にします。時間形式と分数値を同じ列で混在させないことが、集計ミスの予防につながります。
時間の小数表示と端数処理
続いては、時間を小数時間へ変換する方法と、15分単位などで端数を処理する考え方を確認していきます。
| A | B | C |
|---|---|---|
| 作業時間 | 小数時間 | 15分単位 |
| 08:30 | 8.5 | 8.5 |
| 07:38 | 7.63 | 7.75 |
時間を小数へ変換する方法
時間給の計算などで8.5時間のような小数時間が必要な場合は、時刻に24を掛けます。
=B2*24
08:30は1日の24分の8.5なので、数式の結果は8.5です。
07:38のように分がある場合は、小数点以下が長く表示されることがあります。
見やすくするには、=ROUND(B2*24,2)として小数第2位までに丸める方法が適しています。
小数時間の0.5は30分を表し、0.25は15分を表します。
15分単位で切り上げる数式
請求時間や勤怠ルールで15分単位に切り上げる場合は、CEILING関数を使えます。
=CEILING(B2,”0:15″)
B2が07:38なら、結果は07:45になります。
この式では、時刻を15分という基準値の倍数まで切り上げています。
30分単位なら”0:30″、10分単位なら”0:10″に変更できます。
会社の勤怠規則によって切り上げや切り捨ての扱いは異なるため、集計前にルールを確認しましょう。
端数処理の数式を入れる列と、元の実績時間を残す列は分けておくと後から検証しやすくなります。
15分単位で切り捨てる数式
15分単位で切り捨てたいときはFLOOR関数を使います。
=FLOOR(B2,”0:15″)
07:38を切り捨てると07:30になります。
四捨五入をしたい場合は、時刻を分に換算してからROUND関数を使う方法もあります。
たとえば、=ROUND(B2*1440/15,0)*15/1440と入力し、結果セルをh:mm形式に設定すると15分単位の四捨五入ができます。
式はやや長くなりますが、分に直して15で割り、丸め、再び時間へ戻す流れです。
【操作のポイント】端数処理をする前の時刻を上書きしないようにします。元データ列を保持すれば、ルール変更時にも数式を修正できます。
時間計算で表示がおかしい場合の確認項目
続いては、合計時間が正しく表示されないときに確認したい原因と対処法を解説していきます。
| 症状 | 主な原因 | 確認方法 |
|---|---|---|
| 01:30と表示される | 24時間表示の繰り返し | ［h］:mmに変更 |
| #######と表示される | 列幅不足または負の時刻 | 列幅と数式を確認 |
| 合計が0になる | 文字列入力 | 入力形式を確認 |
24時間で戻る表示の確認
合計が25時間30分のはずなのに01:30と表示される場合、SUM関数は正しく動作している可能性が高いです。
セルの書式設定でユーザー定義を開き、［h］:mmへ変更してください。
日単位も表示したい場合は、［h］:mm:ssのように秒まで表示できます。
合計が24時間を超える表では、最も多いトラブルが表示形式の設定漏れです。
シャープ記号や負の時刻の確認
セルに#######が表示される場合、まず列の幅が足りているか確認します。
列見出しの境界をダブルクリックすると、内容に合わせて幅を自動調整できます。
幅を広げても直らない場合は、終了時刻から開始時刻を引いた結果が負になっている可能性があります。
夜勤の計算なら、=MOD(C2-B2,1)へ変更して日またぎに対応させましょう。
負の時刻は通常の時刻表示では表せないため、入力順と日またぎの有無を見直します。
文字列の時刻を数値へ直す方法
時刻が左寄せで表示され、SUM関数の合計に反映されない場合は、入力内容が文字列になっているかもしれません。
セルを選択して警告マークが出ている場合は、数値に変換を選ぶことで直るケースがあります。
文字列の08:30を時刻として変換するには、=TIMEVALUE(B2)を別セルに入力する方法もあります。
変換後のセルをコピーし、値として貼り付ければ、元データを時刻データへ置き換えることも可能です。
【操作のポイント】見た目が同じ08:30でも、時刻データと文字列では計算結果が変わります。セルの配置や数式バーの内容まで確認します。
まとめ エクセルで時間を計算して合計する方法（分単位・24時間以上）
Excelで時間を計算して合計する際は、開始時刻と終了時刻の差を求める数式と、時間表示の設定をセットで考えることが大切です。
通常の勤務時間は=C2-B2で計算でき、日をまたぐ勤務には=MOD(C2-B2,1)が役立ちます。
24時間以上の合計には［h］:mmを設定することで、25時間や100時間を正しく表示できます。
分単位で集計するなら時間に1440を掛け、小数時間で扱うなら24を掛けます。
15分単位の切り上げや切り捨てには、CEILING関数やFLOOR関数を使うと、勤怠ルールに合わせた表を作成しやすくなります。
最後に、合計が合わないと感じたら、表示形式、文字列入力、日またぎ計算の3点を確認しましょう。
正しい数式と書式を使い分けて、勤務時間、残業時間、作業工数を見やすく正確に管理していきましょう。
