エクセルで時間の合計を計算したとき、「24:00」を超えたはずなのに「0:00」や「1:00」などの予想外の表示になってしまった経験はありませんか。
この記事では【Excel】エクセルで時間の足し算が24時間以上になる場合の表示・計算方法（合計・表示形式・24時間表記）について解説していきます。
ポイントは
・表示形式を[h]:mm に変更する
・TEXT関数で文字列として表示する
です。
それでは詳しく見ていきましょう。
エクセルで時間の足し算が24時間以上になる場合の基本知識
エクセルでは、時間は「1日＝1」という数値で内部管理されています。
たとえば「12:00」は0.5、「6:00」は0.25として計算されます。
通常の表示形式のままでは、24時間を超えた部分が切り捨てられてしまい、正しい合計時間が表示されません。
たとえば「25:00」のつもりで合計しても、「1:00」と表示されてしまいます。
これはエクセルの表示形式の仕様によるもので、設定を変更することで解決できます。
エクセルで時間の足し算が24時間以上になる場合の方法1【SUM関数で合計する】
まず、時間の合計にはSUM関数を使います。
以下のサンプルデータをもとに解説します。
| 商品名 | 製造担当 | 作業時間 |
|---|---|---|
| 桜餅 | 田中 | 8:30 |
| 柏餅 | 佐藤 | 7:45 |
| マシュマロ | 鈴木 | 9:00 |
| チョコ | 高橋 | 6:30 |
| 合計 |
A1にヘッダーがある状態で、C2〜C5に各作業時間が入力されているとします。
合計を出したいセル（C6）に以下の数式を入力します。
この数式を入力すると、4つの作業時間の合計が計算されます。
ただし、この時点ではまだ表示形式が通常のままのため、24時間を超えた場合に正しく表示されません。
「8:30＋7:45＋9:00＋6:30」の合計は「31:45」になりますが、表示形式を変更しないと「7:45」と表示されてしまいます。
次の手順で表示形式を変更しましょう。
エクセルで時間の足し算が24時間以上になる場合の方法2【表示形式を[h]:mmに変更する】
24時間以上の時間を正しく表示させるには、表示形式の変更が必要です。
合計が入力されているセル（C6）を選択した状態で、右クリックして「セルの書式設定」を選択します。
「表示形式」タブの「分類」から「ユーザー定義」を選択します。
「種類」の入力欄に以下を入力します。
角括弧「[ ]」でhを囲むことが重要です。
通常の「h:mm」では24時間でリセットされてしまいますが、「[h]:mm」にすることで24時間を超えた時間もそのまま表示できます。
「OK」をクリックすると、「31:45」と正しく表示されます。
秒まで表示したい場合は「[h]:mm:ss」と入力します。
キーボードショートカットでセルの書式設定を開くには、Ctrl＋1を使うと便利です。
エクセルで時間の足し算が24時間以上になる場合の方法3【TEXT関数で文字列として表示する】
TEXT関数の基本的な使い方
合計時間を別のセルに文字列として表示したい場合は、TEXT関数が便利です。
TEXT関数は、数値を指定した書式の文字列に変換する関数です。
書式は以下のとおりです。
「値」には時間が入力されたセルを指定します。
先ほどのサンプルでいえば、合計が入っているC6セルを使って以下のように入力します。
これにより、「31:45」という文字列として別セルに表示できます。
TEXT関数を合計と組み合わせる
SUM関数とTEXT関数を組み合わせて、1つの数式にまとめることもできます。
ただし、TEXT関数の結果は文字列になるため、さらに別の計算に使うことができません。
計算に再利用する必要がある場合は、SUM関数のセルで表示形式を変更する方法（方法2）を使いましょう。
表示だけの目的であれば、TEXT関数は非常に簡単に使えて便利です。
エクセルで時間の足し算が24時間以上になる場合の方法4【時間を数値として計算する】
時間を数値に変換する方法
作業時間の合計を「31.75時間」のように小数点で表示したい場合は、時間データに24を掛けることで変換できます。
エクセル内部では時間は「1日＝1」として管理されているため、24を掛けると時間単位の数値に変換されます。
この数式を入力したセルの表示形式を「数値」に設定すると、「31.75」のように表示されます。
この変換を行う際は、結果のセルの表示形式を「標準」または「数値」にしておくことを忘れないようにしましょう。
時刻形式のままだと予期しない表示になります。
時間・分に分けて表示する
合計時間から「時間」部分だけを取り出したい場合はINT関数を使います。
「分」だけを取り出したい場合は以下の数式を使います。
MINUTE関数は時間データから「分」の部分を整数で返してくれます。
「31:45」なら「45」が返されます。
エクセルで時間の足し算が24時間以上になる場合のよくあるトラブルと対処法
時間として認識されない場合
入力した時間がエクセルに数値として認識されず、文字列として扱われているケースがあります。
この場合、セルの左上に緑の三角マークが表示されることがあります。
文字列として入力されてしまうと、SUM関数を使っても合計が0になってしまいます。
このような場合は、一度データを削除して再入力するか、列全体を選択して「データ」タブの「区切り位置」機能を使って変換しましょう。
負の時間が表示されてしまう場合
時間の引き算をした結果が負の値になると、「########」と表示されることがあります。
エクセルのデフォルト設定では負の時間は表示できないためです。
「ファイル」→「オプション」→「詳細設定」から「1904年から計算する」にチェックを入れると負の時間を表示できますが、他の日付データに影響が出る場合があるため慎重に使用しましょう。
時間の入力形式に気をつける
「8時間30分」を入力する際は、必ず「8:30」の形式で入力します。
「8.30」と入力すると時間ではなく数値として認識されてしまいます。
また「8時30分」のように日本語を混ぜると文字列として認識されるため注意が必要です。
まとめ エクセルで24時間以上になる場合の時間の足し算・表示形式・24時間表記の方法
エクセルで時間の足し算が24時間以上になる場合の表示・計算方法をまとめると
・SUM関数で時間を合計する：=SUM(C2:C5)
・表示形式を[h]:mmに変更する：セルの書式設定から「ユーザー定義」で[h]:mmと入力
・TEXT関数で文字列として表示する：=TEXT(SUM(C2:C5),”[h]:mm”)
・数値として取り出す：=SUM(C2:C5)*24 で時間単位の小数に変換
以上の方法を覚えておくと、シフト表や作業時間の集計など、さまざまな場面で活用できます。
とくに表示形式の「[h]:mm」は基本中の基本ですので、ぜひ覚えておきましょう。
時間データを正しく扱えると、業務効率が大幅にアップするかもしれません。
エクセルの時間計算をうまく活用して、日々の作業をよりスムーズに進めていきましょう！
コメント