【Excel】エクセルの時刻表示がおかしい原因と時間の表示形式を直す方法（時間計算・時間合計・時間足し算）
エクセルで時刻を入力したのに小数や日付として表示されたり、時間の合計が24時間で戻ったりすると、予定表や勤怠表、作業時間表の確認が難しくなります。
時刻の問題は単純な表示形式だけでなく、セルに保存されている値、入力方法、時間計算の数式、合計時間に使う書式が関係する場合があります。
この記事では、エクセルの時刻表示がおかしいときに確認したい原因と、時間表示・時間計算・時間合計を正しく直す方法を順番に解説します。
時刻表示を直す基本ポイントです。
・時刻は1日の何分の1かという数値で保存されます。
・時刻だけなら h:mm、日をまたぐ合計なら [h]:mm を使います。
・計算結果がおかしい場合は、表示形式だけでなく元データの種類も確認します。
予定時刻、勤務時間、休憩時間、残業時間などを扱う表で迷ったときにも役立つ内容です。
エクセルの時刻表示を直す方法【セルの書式設定】
それではまず、時刻が数字や意図しない日付で表示される場合の直し方について解説していきます。
| 開始時刻 | 終了時刻 | 表示が乱れた例 | 正しい表示例 |
|---|---|---|---|
| 9:30 | 18:00 | 0.395833333 | 9:30 |
| 13:15 | 17:45 | 13:15:00 | 13:15 |
時刻を入力しているセルであっても、表示形式が標準や数値になっていると、エクセル内部の値がそのまま見えることがあります。
時刻形式への変更操作
セルを選択し、ホームタブの表示形式一覧から時刻を選ぶか、セルを右クリックしてセルの書式設定を開きます。
分類で時刻を選び、13:30や13:30:55など、用途に合う種類を指定しましょう。
時刻だけを見せたい表では、秒を表示しない h:mm の形式が見やすく、入力した時刻と画面上の表示をそろえやすくなります。
9時5分を9:05と表示したい場合は、時の書式を h、分の書式を mm とする点が重要です。
ユーザー定義で h:mm と入力すると、午前9時5分は 9:05、午後3時40分は 15:40 と表示されます。
【操作のポイント】書式を変更しても元の時刻データは消えませんが、文字列として入力された時刻は書式変更だけでは直らない場合があります。
標準表示で小数になる仕組み
エクセルでは1日を1として、時刻を小数で記録しています。
たとえば正午は1日の半分なので0.5、午前6時は0.25、午前9時30分は約0.395833333です。
時刻の内部値の考え方です。
午前6時は 6÷24 で 0.25 です。
午後0時は 12÷24 で 0.5 です。
午後6時は 18÷24 で 0.75 です。
小数が表示されても時刻データが壊れたとは限らず、書式が標準になっているだけというケースが多くあります。
数値として計算したい場面では小数表示も利用できますが、勤務表やスケジュール表では時刻形式へ戻すと確認しやすくなります。
【操作のポイント】数式バーに0.5と見える場合でも、セルの表示形式を h:mm にすれば12:00として表示できます。
日付まで表示される場合の確認
時刻を入力したセルに1900年1月0日などの日付が付いて表示される場合は、日付と時刻を含む表示形式が設定されている可能性があります。
セルの書式設定で日付分類やユーザー定義の書式を確認し、不要な年月日を外します。
日付を含めない場合は h:mm、日付と時刻を両方残す場合は yyyy/m/d h:mm のように用途を分けるとよいでしょう。
同じ値でも表示形式次第で日付、時刻、連番のいずれにも見えるため、見た目だけで入力ミスと判断しないことが大切です。
【操作のポイント】複数セルを選択して一括で書式を変更すると、時刻列の表示をそろえられます。
時間計算を正しく行う数式
続いては、開始時刻と終了時刻から作業時間を求める数式を確認していきます。
| 日付 | 開始時刻 | 終了時刻 | 実働時間 |
|---|---|---|---|
| 4月1日 | 9:00 | 17:30 | 8:30 |
| 4月2日 | 8:45 | 18:15 | 9:30 |
ここでは1行目を見出し、開始時刻をB列、終了時刻をC列、実働時間をD列に入力する例で説明します。
終了時刻から開始時刻を引く計算
D2に =C2-B2 と入力すると、終了時刻から開始時刻を引いた時間を求められます。
実働時間を求める基本数式です。
=C2-B2
たとえばB2が9:00、C2が17:30なら、計算結果は8:30になります。
数式を入力した後、D2の表示形式を h:mm に設定し、セル右下のフィルハンドルを下へドラッグしてオートフィルを行いましょう。
時間の引き算では、結果セルにも時刻または時間用の表示形式を設定することが必要です。
【操作のポイント】D列に数式をコピーする前に、D2の表示形式を整えておくと、後の行にも同じ形式を引き継げます。
休憩時間を差し引く計算
休憩時間をE列に入力している場合、実働時間のD2には =C2-B2-E2 と入力します。
休憩を含む時間計算の例です。
=C2-B2-E2
開始時刻が9:00、終了時刻が18:00、休憩が1:00なら、実働時間は8:00です。
休憩時間にも1:00のような時刻形式の値を入力します。
60と入力すると60分ではなく60日として扱われるため、分数を数値で入力する列と、時間を時刻形式で入力する列を混在させないようにしましょう。
休憩分を数値で管理している場合は、=C2-B2-E2/1440 のように1日あたりの分数で割る計算が必要です。
【操作のポイント】時間の列は h:mm、分だけの列は数値というように、列ごとのルールを最初に決めるとミスを防げます。
日付をまたぐ勤務時間の計算
午後10時から翌朝5時までのように日付をまたぐ場合、単純な =C2-B2 では負の時間になり、#######と表示されることがあります。
この場合はD2に =MOD(C2-B2,1) と入力すると、差が負になっても1日を加えた正しい時間を返せます。
日付またぎの勤務時間を求める数式です。
=MOD(C2-B2,1)
MOD関数の1は1日を意味し、深夜勤務の時間差を正の値へ戻す役割を持ちます。
終了日時に日付まで入力できる表なら、開始日時と終了日時の差をそのまま引く方法も有効です。
【操作のポイント】深夜勤務がある表では、あらかじめ日付またぎを想定した数式にしておくと、後から修正する手間を減らせます。
時間合計で24時間を超えて表示する書式
続いては、日ごとの時間を合計したときに24時間を超える表示を正しく保つ方法を確認していきます。
| 日付 | 作業時間 |
|---|---|
| 4月1日 | 8:30 |
| 4月2日 | 9:15 |
| 4月3日 | 8:45 |
| 合計 | 26:30 |
日ごとの時間を合計するセルでは、通常の時刻書式ではなく、経過時間用の書式を設定します。
SUM関数による時間合計
合計セルに =SUM(D2:D4) と入力すると、D2からD4までの作業時間を合計できます。
時間合計の基本数式です。
=SUM(D2:D4)
この段階で表示が2:30となる場合、実際の合計が26時間30分でも、時刻として2時30分に戻って表示されています。
SUM関数の計算自体は正しく、問題は合計セルの表示形式にあると考えられます。
【操作のポイント】合計セルだけでなく、月間集計や年間集計のセルにも同じ経過時間書式を設定します。
ユーザー定義の角括弧書式
合計セルを選択してセルの書式設定を開き、表示形式のユーザー定義で [h]:mm と入力します。
角括弧のない h:mm は時刻として時間を0から23の範囲で表示しますが、[h]:mm は経過時間として24時間を超えても累積表示できます。
24時間を超える時間合計の表示形式です。
[h]:mm
26時間30分は 26:30、100時間15分は 100:15 と表示されます。
合計時間に角括弧を付けるかどうかが、24時間で戻る問題を解決する重要点です。
【操作のポイント】秒まで必要な表では [h]:mm:ss を使うと、累積した時間と秒をまとめて表示できます。
時間を小数時間へ換算する方法
8時間30分を8.5時間のように小数で表示したい場合は、時間の合計値に24を掛けます。
時間を小数時間へ換算する数式です。
=SUM(D2:D4)*24
結果セルの表示形式は標準または数値にし、小数点以下の桁数を必要に応じて設定します。
給与計算や工数集計で小数時間が必要な場合に便利ですが、勤務表の見た目用の時間と計算用の小数時間は別列で管理する方法が安全です。
【操作のポイント】小数時間を四捨五入する場合は、=ROUND(SUM(D2:D4)*24,2) のように桁数を明示します。
時間足し算と分単位の入力方法
続いては、指定した時刻に時間や分を足す計算方法について確認していきます。
| 開始時刻 | 加算時間 | 終了予定時刻 |
|---|---|---|
| 9:00 | 1:30 | 10:30 |
| 14:20 | 0:45 | 15:05 |
時間を足す場合も、時刻と時間を同じ時間形式で入力していれば、基本的な足し算だけで処理できます。
時間形式の値を足す計算
開始時刻がB2、加算時間がC2なら、終了予定時刻をD2に表示する数式は =B2+C2 です。
B2に9:00、C2に1:30と入力すれば、D2には10:30が表示されます。
加算時間は1.5ではなく1:30と入力することで、1時間30分として計算されます。
【操作のポイント】結果セルが日付表示になる場合は、D列の表示形式を h:mm に変更します。
分だけを追加する数式
時刻に30分や45分を加える場合、時間を分で指定して1440で割ります。
開始時刻B2に45分を追加する数式です。
=B2+45/1440
1日は24時間、1時間は60分なので、1日あたりの分数は24×60で1440分です。
90分を足すなら =B2+90/1440 と入力します。
分を加算する式では1440を使う理由を理解すると、任意の分数へ応用しやすくなります。
【操作のポイント】加算する分数を別セルに入力する場合は、=B2+C2/1440 のようにセル参照へ置き換えます。
現在時刻を基準にした予定時刻
現在時刻から一定時間後の時刻を表示するには、NOW関数に時間を加算します。
現在時刻の90分後を表示する数式です。
=NOW()+90/1440
NOW関数は日付と時刻を返すため、時刻だけを表示するセルには h:mm、日付も必要なら yyyy/m/d h:mm を設定します。
ファイルを再計算したタイミングで値が更新されるため、固定した予定時刻を残したい用途には注意が必要です。
【操作のポイント】現在時刻を固定入力したいときは、CtrlキーとShiftキーとセミコロンキーで時刻を入力できます。
時刻が文字列になった場合の修正
続いては、見た目は時刻でも計算できない文字列の時刻を確認していきます。
| 入力内容 | データの状態 | 計算可否 |
|---|---|---|
| 9:30 | 時刻値 | 可能 |
| ‘9:30 | 文字列 | 不可 |
| ９：３０ | 全角文字を含む場合あり | 要確認 |
書式を時刻にしても計算されないときは、セルの表示ではなくデータの種類を確認しましょう。
文字列時刻の見分け方
時刻がセルの左側に寄って表示される、SUM関数で合計されない、数式で計算するとVALUEエラーになる場合は、文字列である可能性があります。
セルを選んで数式バーを確認し、先頭にアポストロフィが付いていないか、全角のコロンが含まれていないかを確認します。
見た目が9:30でも、文字列なら時間の引き算や合計には使えません。
【操作のポイント】入力規則や貼り付け元のデータによって文字列化することがあるため、最初の数件で計算結果を確認します。
TIMEVALUE関数による変換
A2に文字列の9:30が入っている場合、別セルに =TIMEVALUE(A2) と入力すると時刻値へ変換できます。
変換結果のセルには h:mm の表示形式を設定します。
文字列の時刻を時刻値へ変換する数式です。
=TIMEVALUE(A2)
変換後に結果をコピーし、値として貼り付けを行えば、元の列を置き換えることもできます。
【操作のポイント】空白や全角文字が混ざるデータでは、まず余分な文字を取り除いてから変換するとエラーを減らせます。
貼り付けと区切り位置の活用
外部システムから貼り付けた時刻がまとめて文字列になった場合は、データタブの区切り位置を使って数値へ変換できることがあります。
対象列を選択し、区切り位置を開いて、区切り文字を指定せずに最後まで進め、列のデータ形式で標準を選びます。
変換後は時刻の表示形式を h:mm に設定し、足し算や引き算ができるかを確かめましょう。
大量の貼り付けデータは、手入力で直すより変換処理を使うほうが効率的です。
【操作のポイント】変換前の列は念のためコピーして残し、結果を比較してから元データを置き換えます。
まとめ エクセルの時刻表示と時間計算を直す方法
エクセルの時刻表示がおかしい場合は、まずセルの表示形式と、時刻が数値として認識されているかを確認しましょう。
時刻だけを表示するセルには h:mm、24時間を超える時間合計には [h]:mm を設定することが基本です。
開始時刻と終了時刻の差は =C2-B2、日付をまたぐ勤務時間は =MOD(C2-B2,1) を使うと計算できます。
時間を足すときは =B2+C2、分だけを加えるときは分数を1440で割る方法が便利です。
表示形式、入力データ、数式という3つの視点で確認すれば、時刻・時間合計・時間足し算の多くの問題を整理できます。
表を作る段階で列ごとの表示形式を統一し、合計セルだけは経過時間書式へ設定しておくと、後からの修正も少なくなるでしょう。
