「エクセル 時間 計算 できない」と検索している方の多くは、次のような悩みを抱えています。
・合計すると変な数字になる
・24時間を超えるとリセットされる
・「#####」と表示される
・引き算がうまくできない
このようなトラブルに悩んだことはありませんか？
時間計算は、通常の数値とは違うルールで管理されているため、正しく設定しないとおかしな結果になります。
この記事では、Excelの時間計算でよくあるトラブルとその解決方法を、初心者にも分かりやすく解説します。
時間計算がおかしくなる原因はこれ
Excelの時間計算がうまくいかない理由は、ほとんどの場合この3つです。
・Excel上の時間の仕組みを理解していない
・表示形式が合っていない
・日またぎやマイナス値が発生している
このあと1つずつ解決していきます。
原因① 時間計算で表示形式が間違っている
■原因
Excelでは、時間は「1日＝1」として管理されています。
そのため、表示形式が「標準」になっていると、時間は小数で表示されます。
例えば「9:00」を標準で表現すると、0.375（=9÷24）となります。
■解決方法
表示形式を「時刻」に設定します。
もしくは、「ユーザー定義」で以下に変更することで解決できます。
h:mm（09:00など、時間の桁数を揃えたい場合は「hh:mm」で設定してください）
原因② 24時間以上で表示がおかしくなる
■原因
通常の時間表示（h:mm）だと、24時間を超えると表示上はリセットしてしまいます。
（実際の値は保持されています）
これは、（h:mm）が「1日の中の時刻」を表示する形式だからです。
■解決方法
表示形式の「h」に角括弧（[]）をつけることで、累計時間を表示することができます。
[h]:mm
角括弧（[]）をつけることで、単位を繰り上げずに累計で表示することができます。
※最近のExcelでは自動で[h]:mmが適用される場合もありますが、意図しない表示になることもあるため、手動で確認するのが確実です。
原因③ 日をまたぐ計算ができない
■原因
22:00〜5:00のように、日をまたぐ場合は終了時間の方が小さくなってしまうことが多いためです。
■解決方法
単純な引き算で計算できない場合、IF関数を使うことで解決できます。
以下のように数式を入力します。
=IF(B2<A2, B2+1-A2, B2-A2)
夜勤やシフト計算では、日をまたぐことがあります。
そのような場合は覚えておくと便利な計算です。
原因④ マイナス時間が表示できない
■原因
Excelはマイナス時間を表示できない仕様となっています。
なぜなら、Excelでは「日付・時刻＝0以上の連続した数値」として管理しているため、マイナス時間を前提にしていないからです。
■解決方法
主な2つの解決方法を説明します。
・TEXT関数を使う
・1904年日付システムを使う
それぞれ解説します。
・TEXT関数を使う
→TEXT関数は、日付や時刻などを特定の表示形式に基づいた文字列に変換する関数です。
※TEXT関数は表示用の文字列に変換するため、そのままでは再計算には使えません
TEXT関数の構文は以下のとおりです。
=TEXT(値,"表示形式")TEXT関数を使ってマイナス時間を表示するには、以下のように入力します。
=IF(I3-J3<0, "-" & TEXT(ABS(I3-J3),"h:mm"), TEXT(I3-J3,"h:mm"))※単純にTEXT関数で「-h:mm」と指定するとエラーになる場合があるため、ABS関数と組み合わせて使用します。
・1904年日付システムを使う
Excelは通常、1900年基準となっています。
ここを1904年基準にすることで、マイナス時間を表示できます。
「ファイル」→「オプション」→「詳細設定」より、「1904年から計算する」にチェックをいれます。
ただこのやり方では、
・日付が約4年ズレてしまう
・他のファイルとの互換性が崩れてしまう
など、トラブルが発生しやすくなります。
本当に時間差のみ計算する場合など、使い方には注意が必要です。
※この設定はブック全体に影響するため、既存データがある場合は注意が必要です。
こんな方法もあるよ、ということで紹介しましたが、実際には、TEXT関数を使うやり方を推奨します。
原因⑤ 「#####」と表示される
■原因
主に2つ考えられます。
・列幅が足りない
・マイナス時間になっている
■解決方法
・列幅が足りない
→この場合は、表示される適切な列幅に広げる必要があります。
・マイナス時間になっている
→マイナスに関しては、マイナス値にならないよう計算式を見直すか、「原因④マイナス時間が表示できない」の対処法を試してみてください。
原因⑥ セルが文字列になっている
■原因
セルが文字列になっている場合、当然計算することはできません。
■解決方法
主な2つの解決方法を説明します。
・表示形式を「標準」に変更
→表示形式が「文字列」になっている可能性があるため、「標準」に変更します。
・VALUE関数を使用する
→VALUE関数は、文字列で保存してある数字や日付を計算可能な数値に変換する関数です。
VALUE関数の構文は以下のとおりです。
=VALUE(テキスト)
原因⑦ 関数の使い方が間違っている（TIME関数）
■原因
TIME関数を使っている場合、引数の設定ミスによって正しく時間が計算できないことがあります。
TIME関数は便利ですが、「時・分・秒」をそれぞれ数値で指定する必要があるため、入力方法を間違えると意図しない結果になることがあります。
■TIME関数の基本
TIME関数は、指定した「時・分・秒」から時刻を作成する関数です。
=TIME(時, 分, 秒)例：
=TIME(9,30,0)このように指定することで、9:30 を表す時刻データになります。
■よくあるミスと対処法
1.時間をまとめて入力してしまう
=TIME(930,0,0)このように入力してしまうと、930時間として扱われてしまいます。
※上の930時間だと、930÷24＝38.75となり、24×75%=18時間として返ってきてしまいます。
時間や分を間違った区切りで入力しないよう注意してください。
2.分や秒が60以上になっている
=TIME(9,75,0)このように入力してもエラーにはなりませんが、「10:15」として処理されてしまいます。
想定外の結果になり、ミスにつながる可能性もあるため、分や秒は60未満を入力するように注意してください。
3.24時間以上の値を指定している
=TIME(25,0,0)この場合、Excelでは1:00と表示されてしまいます。（24時間を超えると表示上リセットするため）
TIME関数はあくまで「時刻用」として使用し、合計時間には使用しないほうが無難です。
■ポイントまとめ
・TIME関数は「時刻」を作る関数
・合計時間の計算には向いていない
・引数は必ず「時・分・秒」に分けて入力する
時間計算でエラーが出る場合は、TIME関数の使い方も確認してみましょう
よくある質問（FAQ）
エクセルで時間の合計が正しく表示されません
表示形式が原因の可能性が高いです。
合計が24時間を超える場合、
h:mmではなく [h]:mmを設定してください。
時間を引き算すると「#####」と表示されます
主な原因は2つあります。
・列幅が足りない
・マイナス時間になっている
特にマイナス時間は通常表示できないため、
計算式や設定を見直しましょう。
日をまたぐ時間（夜勤）がうまく計算できません
IF関数で対応できます。
例）=IF(B1<A1, B1+1-A1, B1-A1)
終了時間が開始時間より小さい場合に、
1日分を加算することで正しく計算できます。
時間が数値（0.5など）で表示されます
表示形式が「標準」になっています。
セルの表示形式を「時刻（hh:mm）」
に変更してください。
マイナスの時間を表示する方法はありますか？
通常設定では表示できませんが、
以下の方法があります。
・1904年日付システムを使用
・TEXT関数で表示
例）=TEXT(A1-B1,”-h:mm”)
時間を分や秒に変換することはできますか？
可能です。
分に変換 → =A1*1440
秒に変換 → =A1*86400
勤務時間から休憩時間を引くには？
以下のように計算します。
=終了時間 – 開始時間 – 休憩時間
まとめ
Excelの時間計算がうまくいかない原因は、この3つがほとんどです。
・表示形式のミス
・24時間制限
・日またぎやマイナス時間
「時間計算がおかしい」と感じたときは、まず表示形式を確認することが重要です。
以下のポイントを押さえれば、時間計算で困ることはほとんどなくなります。
・ [h]:mm を使う
・ 日またぎはIF関数
・ 時間は「1日＝1」で管理される
Excelでの時間計算に困ったときは、本記事を参考にしてみてください。
最後まで読んでいただき、ありがとうございました。
コメント