【Excel】エクセルで時間を24時間以上表示する方法と合計が正しく出ない原因

Excelで勤務時間や作業時間を集計すると、合計が24時間を超えた途端に表示が0時台へ戻ることがあります。

これは計算ミスとは限らず、セルの表示形式や入力値の扱いが原因になっているケースが大半です。

24時間を超える累計時間は、通常の時刻表示ではなく経過時間として書式設定することが重要です。

この記事では、時間を24時間以上で表示する方法、SUM関数で合計が正しく出ない原因、勤務時間の集計に使いやすい数式を詳しく解説します。

入力値と表示形式を分けて考えると、時間計算のトラブルは解決しやすくなります。

 

エクセルで24時間以上を表示するユーザー定義書式

それではまず、24時間を超えた合計を正しく表示するための書式設定について解説していきます。

日付	作業時間	累計時間
9月1日	8:30	8:30
9月2日	9:15	17:45
9月3日	8:45	26:30

通常のh:mm形式では26時間30分が2:30と表示されますが、[h]:mm形式なら26:30のまま確認できます。

 

セルの書式設定からの変更手順

具体的な変更手順を確認していきます。

合計時間が入っているセルを選択し、右クリックして「セルの書式設定」を選びます。

表示形式タブで「ユーザー定義」を選び、種類の入力欄に[h]:mmと入力しましょう。

角括弧で囲んだ[h]が、24時間を超えても時間を繰り上げずに表示する指定です。

秒まで記録したいときは[h]:mm:ssを指定します。

設定後にOKを押すと、数式そのものを変えずに表示だけを修正できます。

 

hと[h]で異なる時間表示

続いては、書式記号の違いを確認していきます。

Excelでは1日を1として時間を管理しており、12時間は0.5、24時間は1、36時間は1.5として保存されます。

h:mmは日付の中の時刻部分だけを取り出して表示するため、1日を超えた部分が見えません。

一方で[h]:mmは経過した時間数を合計して表示するため、24、48、100といった時間もそのまま表示できます。

値が壊れているのではなく、見せ方が時計表示になっているだけという点が大切です。

日数も併せて表示したい場合は、[h]:mmのほか、d日 h時間 mm分のようなユーザー定義書式を利用できます。

 

表示形式をコピーする方法

続いては、複数のセルへ書式をそろえる方法を確認していきます。

すでに[h]:mmを設定したセルがあるなら、そのセルをコピーして対象範囲へ貼り付ける方法が手軽です。

貼り付け時のオプションで「書式設定」を選ぶと、数式や値を変えずに表示形式だけを反映できます。

書式のコピー貼り付けが難しい場合は、ホームタブの書式のコピー貼り付け機能も便利です。

合計欄だけh:mmのまま残ると、計算が合わないように見える原因になります。

 

SUM関数で時間の合計が正しく出ない原因

続いては、SUM関数で時間の合計が期待どおりにならない主な原因を確認していきます。

セル	内容	確認結果
B2	8:00	数値の時刻
B3	8:30	数値の時刻
B4	8時間	文字列の可能性

SUM関数は数値を合計するため、見た目が時間でも文字列として保存されているセルは正しく集計できません。

 

時間が文字列として入力されている状態

それでは、文字列扱いになる入力例について解説していきます。

セルに8時間、8h、午前8時のように単位や文字を含めて入力すると、Excelは時刻の数値として認識できない場合があります。

また、先頭にアポストロフィが付いた’8:30や、全角コロンを使った8：30も文字列になることがあります。

セルの左上に緑色の三角形が表示される場合は、数値が文字列として保存されている可能性があります。

セルの表示形式を時刻に設定してから半角コロンで入力すると、集計用の値として認識されやすくなります。

 

SUM関数の範囲指定と非表示行

続いては、集計範囲の確認について解説していきます。

たとえばB2からB31までの日別作業時間を集計するなら、合計セルには=SUM(B2:B31)と入力します。

範囲の最終行が1行ずれていたり、途中のセルを除外したりすると、書式が正しくても合計値は一致しません。

フィルターで非表示になった行もSUM関数では合計される点にも注意しましょう。

表示されている行だけを合計したい場合は、集計目的に応じてSUBTOTAL関数やAGGREGATE関数を検討します。

数式バーで範囲の色付き枠を確認すると、対象範囲の見落としを防げます。

 

負の時間と日付システムの関係

続いては、マイナスの時間が表示されない理由を確認していきます。

終了時刻から開始時刻を引いた結果がマイナスになると、標準的な1900年日付システムでは#######と表示されることがあります。

これは列幅不足とは限らず、Excelが負の時刻を通常の時刻として表示できないことによる現象です。

夜勤のように日付をまたぐ勤務では、終了時刻が開始時刻より小さく見えるため、単純な引き算では負の値になります。

日付またぎの勤務時間は、翌日分の24時間を加える数式で処理する必要があります。

 

勤務時間を合計する数式と日付またぎの処理

続いては、勤務時間表で使いやすい数式と入力方法について解説していきます。

日付	開始	終了	勤務時間
9月1日	9:00	18:00	8:00
9月2日	22:00	6:00	8:00

休憩時間を別列に入力しておくと、日勤と夜勤を同じ表で管理できます。

 

休憩時間を差し引く基本数式

それでは、日中勤務を計算する基本式について解説していきます。

1行目を見出しとし、B列に開始時刻、C列に終了時刻、D列に休憩時間、E列に実働時間を入力する構成を考えます。

E2には=C2-B2-D2と入力すると、終了時刻から開始時刻と休憩時間を引いた実働時間を計算できます。

数式を入力したE2には[h]:mmを設定し、セル右下のフィルハンドルを下へドラッグしてコピーします。

数式を下方向へコピーすると、B2やC2などの参照はB3、C3のように自動で変化します。

 

日付をまたぐ夜勤の計算式

続いては、夜勤の勤務時間を求める方法を確認していきます。

開始が22:00で終了が翌朝6:00の場合、=C2-B2-D2では負の時間になってしまいます。

このようなケースでは、=MOD(C2-B2,1)-D2という数式を使います。

休憩が1:00なら、22:00から翌6:00までの8時間から差し引かれ、実働は7:00です。

日付またぎの判定をIF関数で分けるより、MOD関数なら日勤と夜勤の両方に使える点が便利です。

 

月間合計と時間単価の計算

続いては、月間の合計時間と給与計算への活用を確認していきます。

日別の実働時間がE2からE31にある場合、月間合計は=SUM(E2:E31)で求められます。

この合計セルの表示形式を[h]:mmにすることで、たとえば168時間30分を168:30と表示できます。

時間を小数の時間数へ換算したいときは、合計時間に24を掛けます。

Excelの時刻は1日を基準にした値なので、24を掛けると時間数へ換算できる仕組みです。

時給がF2に入力されている場合は、=SUM(E2:E31)*24*$F$2で概算の給与額を求められます。

F2を絶対参照にするための$記号は、数式を下へコピーする際に時給セルを固定したいときに役立ちます。

 

時間表示形式と数値形式の使い分け

続いては、目的に応じた時間表示形式の選び方について解説していきます。

用途	おすすめの表示形式	表示例
開始・終了時刻	h:mm	9:00
累計勤務時間	[h]:mm	168:30
給与計算用時間	0.00	168.50

見た目の分かりやすさと計算目的を分けて設計すると、勤怠表の精度が上がります。

 

分を含む時間を小数へ換算する方法

それでは、時間と分を小数の時間数へ変換する方法を解説していきます。

7時間30分を給与計算用の時間数として扱うとき、7.30ではなく7.5と計算する必要があります。

時刻データがE2にあるなら、=E2*24で7:30を7.5へ変換できます。

60分は0.6時間ではなく1時間であるため、分をそのまま小数点以下へ置き換えてはいけません。

換算後のセルは時刻形式ではなく、数値形式で小数点以下の桁数を指定します。

 

秒を含む時間の集計形式

続いては、秒単位の作業時間を集計する場合を確認していきます。

動画編集、コールセンター、製造工程の記録などでは、秒まで含めた時間の集計が必要になることがあります。

開始時刻と終了時刻に秒が含まれる場合でも、基本的な引き算やSUM関数の考え方は同じです。

合計セルの表示形式だけを[h]:mm:ssに設定してください。

秒を含む累計時間でも、時間部分を[h]にすることで24時間以上の表示に対応できます。

丸めが必要な場合は、時間を秒数や分数へ換算してからROUND関数を使う方法もあります。

 

日数を含む長時間の表示形式

続いては、数百時間を超える長期集計の表示を確認していきます。

プロジェクト全体の工数や年間の残業時間では、100時間や1,000時間を超える値を扱うことがあります。

[h]:mmは時間数を累計で示せるため、長時間の集計にもそのまま使用できます。

日数を前面に出したい場合は、[h]:mmではなくd日 h:mmのような表示形式を設定する方法があります。

日数表示ではdが経過日数、hがその日の時刻部分になるため、用途に合わせた確認が必要です。

人月換算など別の単位が必要なケースでは、元の時刻データを残したうえで換算用の列を追加しましょう。

 

時間集計で起こりやすい入力ミスと確認項目

続いては、時間計算の結果を確認するときに見落としやすいポイントを解説していきます。

確認項目	よくある状態	対処
入力値	文字列の8時間	8:00で入力
合計書式	h:mm	[h]:mmへ変更
数式範囲	最終行が不足	参照範囲を見直す

問題が起きたセルだけを見るのではなく、入力、数式、表示形式の順に確認すると原因を切り分けやすくなります。

 

0:00と空白セルの扱い

それでは、空白セルと0:00の違いについて解説していきます。

0:00は時間がゼロである数値ですが、空白セルは入力されていない状態です。

SUM関数は空白セルを基本的に無視するため、合計だけなら大きな問題にならないこともあります。

ただし、平均時間を計算したり、未入力日を数えたりする場合は、空白と0:00で結果が変わります。

休みの日、未入力の日、勤務時間がゼロの日をどのように区別するかを表の運用前に決めておきましょう。

 

セル参照のずれと数式エラー

続いては、コピーした数式で起きる参照ずれを確認していきます。

日別の勤務時間を計算する数式をコピーした後、特定の行だけ参照先が異なることがあります。

数式バーをクリックすると、参照中のセルが色付き枠で表示されるため、開始時刻、終了時刻、休憩時間の参照を確認できます。

行を途中に挿入した場合や、別シートからコピーした場合は特に注意が必要です。

合計値が急に大きい、または小さいときは、表示形式より先に数式の参照範囲を確認しましょう。

数式を手入力で修正するより、正しい行の数式をコピーして直すほうがミスを減らせるかもしれません。

 

計算結果と表示値の照合

続いては、合計結果を検算する方法について解説していきます。

時間の合計が不安な場合は、合計セルを一時的に標準表示へ変更すると、Excel内部で保存されている日数の値を確認できます。

たとえば36時間は1.5、12時間は0.5として表示されます。

この値に24を掛けて想定する時間数と照合すると、数式の問題か表示形式の問題かを判断できます。

標準表示で正しい日数値が出ていれば、合計計算ではなく表示形式を修正すべき場面です。

 

まとめ エクセルで時間を24時間以上表示する方法と合計が正しく出ない原因

Excelで時間を24時間以上表示したいときは、合計セルの表示形式を[h]:mmまたは[h]:mm:ssへ変更します。

h:mmは時刻を示す書式なので、24時間を超えると時計のように0時台へ戻ります。

月間合計や累計時間には[h]を使うことを覚えておけば、26:30や168:00のような値も正しく確認できます。

合計が正しく出ない場合は、時間が文字列になっていないか、SUM関数の範囲に漏れがないか、負の時間が発生していないかを確認しましょう。

日付をまたぐ勤務には=MOD(終了時刻-開始時刻,1)-休憩時間の考え方が役立ちます。

時間表示用のセルと、給与計算用に24を掛けた小数時間のセルを分けると、勤怠表や作業時間表をより正確に管理できます。