Excelで時刻や時間を扱っていると、「時間の表示がおかしくて数字になってしまう」「時間の引き算をしたら負の値になってエラーが出る」「時間の平均を計算したら正しい値にならない」といったトラブルに悩まされることがあります。時刻・時間のデータはExcel独自のシリアル値という仕組みで管理されているため、表示形式の理解が解決への近道でしょう。
この記事では、Excelの時刻・時間の表示がおかしくなる原因と、時間の引き算・平均の計算も含めた具体的な対処法をわかりやすく解説していきます。時刻・時間のトラブルをひとつずつ解決していきましょう。
Excelの時刻・時間の表示がおかしい基本的な原因と対処法
それではまず、Excelの時刻・時間の表示がおかしくなる基本的な原因と対処法について解説していきます。
時刻の表示がおかしくなる最も多い原因は、セルの表示形式が時刻形式になっておらず、シリアル値がそのまま数値で表示されてしまうことです。Excelでは時刻を0から1の間の小数値（シリアル値）として管理しているため、表示形式が正しく設定されていないと意味不明な数値が表示されます。
時刻が数値で表示される場合に表示形式を修正する方法
「0.5」や「0.75」のような数値が表示されている場合、セルに時刻データは正しく入力されているが表示形式が数値になっています。
②「ホーム」タブ→「数値」グループのプルダウンをクリックする
③「時刻」を選択する
④または「セルの書式設定」（Ctrl+1）→「表示形式」タブ→「時刻」を選択して形式を指定する
⑤「h:mm」「h:mm:ss」など目的の表示形式を選択して「OK」をクリックする
Excelの時刻は内部的にシリアル値で保存されており、表示形式を変えるだけで正しく時刻として表示されます。データを入力し直す必要はなく、表示形式の変更だけで解決できるでしょう。
24時間を超える時間を正しく表示する方法
勤務時間の合計など24時間を超える時間を計算すると、「25:30」と表示されるべきところが「1:30」と表示されてしまうことがあります。これは時刻形式が24時間を超えると翌日の時刻として表示されてしまうためです。
②「表示形式」タブ→「ユーザー定義」を選択する
③「種類」の入力欄に「[h]:mm」と入力する（角括弧でhを囲むことが重要）
④「OK」をクリックして25:30のように24時間を超えた時間が正しく表示されることを確認する
⑤秒も表示したい場合は「[h]:mm:ss」と入力する
時刻の入力が日付になってしまう場合の対処法
「8:30」と入力したのに「8月30日」と表示されてしまう場合、Excelが時刻ではなく日付として認識しています。
②または入力後にCtrl+1で「セルの書式設定」を開き「時刻」形式に変更する
③「8:30」と入力する際は必ずコロン（:）を使って入力する
④「8.30」のようにピリオドで区切ると時刻として認識されないため注意する
⑤AM・PMを使う場合は「8:30 AM」のようにスペースを入れて入力する
時刻の入力はコロン（:）区切りが基本です。事前にセルの書式設定を「時刻」に変更してから入力すると、誤認識を防げます。特に日付と時刻が混在するデータを扱う場合は書式設定を先に確認しておくとよいでしょう。
| 症状 | 原因 | 対処法 |
|---|---|---|
| 時刻が小数値で表示される | 表示形式が数値になっている | 表示形式を「時刻」に変更する |
| 25時間以上が正しく表示されない | 通常の時刻形式では24時間でリセットされる | ユーザー定義で「[h]:mm」に変更する |
| 時刻入力が日付になる | Excelが日付として認識している | 入力前に表示形式を「時刻」に変更する |
Excelの時間の引き算がおかしい原因と対処法
続いては、Excelの時間の引き算がおかしくなる原因と対処法を確認していきます。
時間の引き算で「######」エラーや負の値が表示される場合、引き算の結果が負の値になっていることやExcelの時刻計算の仕組みが原因として挙げられます。時間の引き算には特有の注意点があるため、正しい方法を理解しておくことが大切でしょう。
時間の引き算で######エラーが出る場合の対処法
終了時刻から開始時刻を引いたときに「######」が表示される場合、計算結果が負の値になっている可能性があります。Excelの標準設定では負の時刻を表示できないためエラーになります。
②「次のブックを計算するとき」セクションの「1904年から計算する」にチェックを入れる
③「OK」をクリックして負の時間が表示されるか確認する
④または負の時間を扱う場合はTEXT関数とIF関数を組み合わせて表示する
例：=IF(B1-A1<0, “-“&TEXT(ABS(B1-A1),”h:mm”), TEXT(B1-A1,”h:mm”))
「1904年から計算する」オプションを有効にすると負の時刻が表示できるようになりますが、このオプションを変更するとブック内のすべての日付データがずれる可能性があります。既存のデータへの影響を必ず確認してから設定を変更しましょう。
日をまたぐ時間の引き算を正しく計算する方法
夜間シフトなど日をまたぐ勤務時間（例：23:00〜翌7:00）を計算すると、マイナスの値になってしまいます。日をまたぐ時間差はMOD関数を使って正しく計算できます。
=B1-A1（B1=7:00、A1=23:00の場合 → 負の値になる）MOD関数を使った正しい計算方法
=MOD(B1-A1, 1)
→ 日をまたいでも正しく8時間と計算されるまたは1を加算する方法
=B1-A1+IF(B1<A1, 1, 0)
→ 終了時刻が開始時刻より小さい場合に1日分を加算する
MOD関数を使った計算方法はシンプルで汎用性が高く、日をまたぐ時間計算の定番手法です。夜間勤務の時間計算など日をまたぐデータを扱う場合は積極的に活用しましょう。
時間の引き算結果を分・秒単位で表示する方法
時間の差を時間単位ではなく分や秒の数値として取り出したい場合は、シリアル値に変換係数を掛けることで計算できます。
=INT((B1-A1)*24)
→ 2時間30分の場合 → 2分として取り出す
=(B1-A1)*24*60
→ 2時間30分の場合 → 150（分）秒として取り出す
=(B1-A1)*24*60*60
→ 2時間30分の場合 → 9000（秒）
HOUR・MINUTE・SECOND関数を使う方法
=HOUR(B1-A1) → 時間部分のみ取り出す
=MINUTE(B1-A1) → 分部分のみ取り出す
=SECOND(B1-A1) → 秒部分のみ取り出す
時間の差を数値として活用したい場合は、シリアル値に24を掛けることで時間単位の数値に変換できます。給与計算や作業効率の分析など、時間を数値として扱いたい場面で非常に役立つ方法でしょう。
Excelの時間の平均計算がおかしい原因と対処法
続いては、Excelの時間の平均計算がおかしくなる原因と対処法を確認していきます。
時間データの平均をAVERAGE関数で計算しても、表示形式が正しく設定されていないと意味不明な数値が表示されてしまいます。時間の平均計算には表示形式の設定が欠かせません。
AVERAGE関数で時間の平均を正しく計算・表示する方法
時間データの平均はAVERAGE関数で計算できますが、結果を表示するセルの書式設定を正しくしておく必要があります。
②平均を表示したいセルに「=AVERAGE(A1:A10)」と入力する
③結果が数値や日付で表示される場合はCtrl+1で「セルの書式設定」を開く
④「表示形式」タブ→「時刻」または「ユーザー定義」で「h:mm」を選択する
⑤平均時間が正しく「h:mm」形式で表示されることを確認する
AVERAGE関数自体は時間データでも正しく動作します。計算結果のセルに時刻の表示形式を設定するだけで正しい平均時間が表示されるため、まず表示形式を確認してみましょう。
時間の平均が24時間を超える場合の対処法
複数の時間データの平均が24時間を超える場合、通常の時刻形式では正しく表示されません。
②「表示形式」タブ→「ユーザー定義」を選択する
③「種類」に「[h]:mm」と入力する
④「OK」をクリックして24時間を超えた平均時間が正しく表示されることを確認する
⑤さらに詳細な表示が必要な場合は「[h]:mm:ss」と入力して秒まで表示する
24時間を超える可能性がある時間集計では常に「[h]:mm」形式を使う習慣をつけておくと、表示のトラブルを未然に防げます。勤務時間の月次集計など合計時間が大きくなるデータには特に重要な設定です。
文字列として入力された時刻データを時間計算に使う方法
「8:30」のような時刻が文字列として入力されている場合、AVERAGE関数やSUM関数では正しく計算されません。TIMEVALUE関数を使って時刻データに変換する必要があります。
②TIMEVALUE関数で時刻データに変換する
例：=TIMEVALUE(A1) → 「8:30」という文字列を時刻のシリアル値に変換
③変換後のセルの表示形式を「時刻」に設定する
④大量のデータを変換する場合は補助列にTIMEVALUE関数を入力し、値として貼り付けてから元列に上書きする
⑤変換後のデータでAVERAGE関数やSUM関数が正しく機能するか確認する
まとめ
この記事では、Excelの時刻・時間の表示がおかしくなる原因と、時間の引き算・平均の計算も含めた具体的な対処法をまとめて解説しました。
時刻のトラブルはセルの表示形式が原因であることがほとんどです。通常の時間表示には「h:mm」、24時間を超える場合は「[h]:mm」を使うことが基本ルールとして覚えておきましょう。日をまたぐ引き算はMOD関数、文字列として入力された時刻はTIMEVALUE関数で対処できます。
時刻・時間の計算はExcelの中でも独特のルールが多い分野ですが、シリアル値と表示形式の関係を理解しておけばほとんどのトラブルに対応できるでしょう。今回紹介した手順を参考に、時間データを正確に扱えるExcel環境を整えてみてください。
