Excelで日付・時間の引き算がおかしい原因と直し方|####・マイナス・日またぎ

スポンサーリンク

勤怠表で「退勤 − 出勤」を引いたのに、セルに「####」が並んで手が止まっていませんか。Excel の日付・時間の引き算がおかしいとき、原因は症状ごとにはっきり分かれています。

次のような症状に心当たりはないでしょうか。

  • 日数を出したいのに「1900/1/14」のような日付が出る
  • 引き算した瞬間に #VALUE! が表示される
  • 夜勤(22:00→翌6:00)の行だけ「####」になる
  • 時間数に直したら「0:00」や「8.333333」のような値になる

放っておくと、勤務時間や残業の集計がまるごと信用できなくなります。手で数字を打ち直しても、翌月また同じところでつまずくはず。

この記事では、症状から原因を逆引きできる早見表を冒頭に置きました。全症状を同じ勤怠サンプルで「直す前の式・結果」と「直した後の式・結果」に並べています。今出ている症状の節だけ読めば、その場で直せますよ。

【早見表】Excelで日付・時間の引き算がおかしいときの症状別・原因と直し方

症状見えている状態原因直し方の式・操作
日付の引き算が日付で出る1900/1/14結果セルが日付の書式表示形式を「標準」にする
日付の引き算で #VALUE!#VALUE!片方が日付として読めない文字列=ISNUMBER(K2) で診断し、入力し直すか =DATEVALUE(K2)
結果が変わらない0 や前の値のまま/式が文字で見える計算方法が手動/セルが文字列の書式計算方法を自動に戻す・F9/書式を標準にして再入力
時間の引き算が ############結果がマイナスの時刻=IF(E3 などで正負を分ける
日をまたぐ勤務で合わない########退勤の時刻が出勤より小さい=MOD(C3-B3,1)-D3
時間数・分にすると合わない0:00/8.333333書式が時刻のまま/割り切れない端数書式を標準にして =ROUND(E2*24,2)

迷ったら、まず結果のセルを選んで表示形式を「標準」にしてみてください。日付や時刻の正体は「シリアル値」という数値なので、標準にすると中身の数字が見えます。

シリアル値とは、1900/1/1 を「1」として数える日付の通し番号のこと。時刻は 1 日を「1」とした小数で、12:00 なら 0.5 になります。

対応バージョンの注意

対応バージョン: この記事の式は Microsoft 365 と Excel 2024/2021/2019/2016 を想定しています。
DATEVALUE 関数の公式の対象は Microsoft 365 と 2016 以降の Windows 版・Mac 版です。公式ページの対象一覧に、Web 版の記載はありません。
1904 年日付システムは、ブック単位の設定です。Windows デスクトップ版では「ファイル > オプション > 詳細設定」で切り替えます。Web 版・Mac 版の手順は、この記事では扱いません。

この記事で使う勤怠サンプル

2 行目が日勤、3 行目が夜勤(22:00 に出勤して翌朝 6:00 に退勤)です。B〜D 列は「9:00」のように時刻として入力しています。

行A 日付B 出勤C 退勤D 休憩E 実働F 所定
22024/11/19:0018:001:008:00 になるはず8:00
32024/11/222:006:001:007:00 になるはず8:00

日付の引き算には、同じシートの集計期間のセルを使います。J2 が開始日の 2024/11/1、K2 が締め日の 2024/11/15 です。L2 で日数を出し、正解は 14 日になります。

日付の引き算が日付表示になる・#VALUE!になる・0のまま変わらない

日付どうしの引き算は、式そのものは =K2-J2 だけで合っています。この節の 3 つの症状は、どれも書式・入力・設定のどこかに原因があります。

結果が「1900/1/14」のような日付で出る(表示形式を標準へ)

直す前(L2 が日付の書式)
L2: =K2-J2
→ 1900/1/14

直した後(L2 の表示形式を「標準」に変更)
L2: =K2-J2
→ 14

実は、引き算の結果「14」は最初から正しいんです。結果セルが日付の書式だと、14 を「1900/1/1 から数えて 14 番目の日」として表示してしまいます。

日付を入れていたセルを使い回すと、結果セルが日付の書式のまま残っていることがあります。参照元の書式を Excel が引き継ぐ場合もあるので、式が合っているのに日付が出たら書式を疑いましょう。

直し方は、L2 を選んでホームタブの「数値の書式」で「標準」を選ぶだけ。Ctrl+1 で開く「セルの書式設定」の「表示形式」から「標準」を選んでも同じです。

逆に、日付を入れたはずのセルが「45597」のような 5 桁の数字になる症状もあります。仕組みは同じシリアル値なので、Excelで日付が数字(シリアル値)になる原因と直し方で確認してみてください。

#VALUE! になる(文字列の日付・DATEVALUE・ISNUMBERで診断)

直す前(K2 が文字列の日付)
L2: =K2-J2
→ #VALUE!

診断
M2: =ISNUMBER(K2)
→ FALSE(K2 は数値ではなく文字列)

見た目は日付でも、中身が文字列になっていることがあります。勤怠システムから貼り付けた日付や、CSV から取り込んだ日付が代表例です。

Excel は日付として読めない文字列を引き算できず、#VALUE! を返します。余計な文字やスペースが混ざっていると、読めなくなることがあります。どの書き方なら読めるかは PC の地域設定にも左右されるため、まずは診断から始めましょう。

  • =ISNUMBER(K2) が TRUE なら数値(正しい日付)、FALSE なら文字列
  • 書式を変えていなければ、文字列は左寄せ、日付は右寄せで表示される

文字列だとわかったら、K2 を「2024/11/15」と打ち直すのが確実です。数が多いときは、DATEVALUE 関数で日付のシリアル値に変換できます。

直した後(DATEVALUE で変換してから引く)
L2: =DATEVALUE(K2)-J2
→ 14

DATEVALUE 自体が #VALUE! を返すなら、文字列の書き方が日付として読めない形です。元のデータから余計な文字を取り除いてください。関数の詳しい使い方はDATEVALUE関数の使い方、引き算以外の原因は#VALUE!エラーの直し方にまとめています。

結果が0や前の値のまま(計算方法が手動・文字列の書式)

日付を入れ替えたのに L2 が 0 や前の値のまま、という症状です。原因は 2 つ考えられます。

1 つ目は、ブックの計算方法が「手動」になっている場合。日付を入れる前に式を作っていると、0 のまま残ります。

数式タブの計算方法の設定(表記は版によって異なることがあります)を「自動」に戻してください。手動のままでも、F9 キーを押せば再計算されます。

2 つ目は、L2 が文字列の書式(表示形式が「文字列」)になっている場合です。このときは結果ではなく =K2-J2 という式がそのまま文字で見えます。表示形式を「標準」に変えたら、L2 を選んで F2 キー → Enter で入れ直しましょう。

日付計算でどの関数を使えばいいか迷ったら、Excelの日付計算を完全攻略も参考になります。

時間の引き算が「####」になる(マイナスの時間)

原因は列幅ではなく、結果がマイナスだから

夜勤の 3 行目で、所定の 8:00 との差を出してみます。実働の E3 は 7:00 なので、差は 1 時間のマイナスです。

直す前
G3: =E3-F3
→ ########(列幅を広げても消えない)

####(シャープ)というと列幅不足が有名ですよね。ただ、列をいくら広げても消えないなら、結果がマイナスの時刻になっているサインです。

Windows 版の既定の 1900 年日付システムでは、マイナスの時刻を表示できません。時刻の書式のままだと #### になるんです。

値そのものは計算できているので、G3 を「標準」にすると「-0.04167」のようなマイナスの小数が見えるはず。

列幅不足なども含めた #### の全パターンは、Excelのセルに「####」が表示される原因と直し方で確認できます。

直し方: IF/ABS/TEXT で処理(TEXTは文字列になり再計算不可)

マイナスを表示させる方法は 3 つあります。それぞれの結果と使いどころは次の表のとおり。

方法式(3 行目)結果合計・×24 に使えるか
IF で不足と超過を分ける=IF(E31:00(不足)使える
ABS で符号を外す=ABS(E3-F3)1:00使える(ただし符号が消える)
TEXT で「-」を付ける=IF(E3-1:00使えない(文字列)

集計に使うなら IF がおすすめです。「不足」列と「超過」列を分けておけば、どちらもマイナスにならず、月末に SUM で合計できます。

直した後(G 列=不足、H 列=超過)
G3: =IF(E3<F3,F3-E3,0)  → 1:00
H3: =IF(E3>F3,E3-F3,0)  → 0:00
G2: =IF(E2<F2,F2-E2,0)  → 0:00(日勤は所定どおり)

ABS は差の大きさだけを返します。不足か超過かの区別が消えるため、別の列で符号を判定する前提で使いましょう。詳しくはABS関数の使い方をどうぞ。

TEXT の式は「-1:00」と見た目どおりに表示できる反面、結果が文字列になります。SUM で合計しても数に入らず、×24 などの再計算にも使えません。

表示専用の列と割り切るのが安全です。書式の指定方法はTEXT関数の使い方にまとめています。

1904年設定は最後の手段(日付が4年1日ずれる副作用)

ブックを「1904 年日付システム」に切り替えると、マイナスの時刻が表示できるようになります。Windows デスクトップ版の手順は次のとおりです。

  1. 「ファイル」→「オプション」→「詳細設定」を開く
  2. 「次のブックを計算するとき」で対象のブックを選ぶ
  3. 1904 年日付システムを使う項目にチェックを入れる

ただし副作用が大きい点に注意してください。2 つの日付システムは基準日が 1,462 日(4 年と 1 日)ずれています。入力済みの日付は、このずれの分だけ違う日付で表示されることがあります。

サンプルの 2024/11/1 なら、計算上は 2028/11/2 として表示されます。設定はブック単位なので、日付の入った勤怠表では使わず、IF などの式で処理しましょう。

なお、別の環境で作られたブックを開くと、この設定が自動でオンになっていることがあります。日付が 4 年ずれて見えたら、この設定を確認してみてください。

日をまたぐ勤務(22:00→翌6:00)の時間が合わない

直す前
E3: =C3-B3-D3
→ ########(6:00 − 22:00 − 1:00 = −17:00)

時刻だけを入力していると、退勤の 6:00 は「出勤と同じ日の朝 6 時」として扱われます。22:00 より前の時刻なので、引き算の結果がマイナスになるわけです。

直し方は、式で「翌日」を補う方法が 2 つ。日付ごと入力している表なら、そもそも直す必要はありません。

MOD 方式 =MOD(退勤-出勤,1)

直した後
E3: =MOD(C3-B3,1)-D3  → 7:00
E2: =MOD(C2-B2,1)-D2  → 8:00

MOD は割り算の余りを返す関数です。1 で割った余りは必ず 0 以上 1 未満になるので、-16 時間(-16/24)が 8 時間(8/24)に変わります。

ちょっとむずかしく見えますが、やっていることはシンプルです。「マイナスになったら 24 時間を足す」のと同じ結果になるんです。日勤の 2 行目にも同じ式が使えるので、列全体にコピーできます。

注意点は、出勤と退勤が同じ時刻だと 0 になること。24 時間ちょうどの勤務も 0:00 になってしまいます。MOD の仕組みはMOD関数の使い方で詳しく解説しています。

IF 方式 =IF(退勤

直した後
E3: =IF(C3<B3,C3+1,C3)-B3-D3  → 7:00
E2: =IF(C2<B2,C2+1,C2)-B2-D2  → 8:00

「退勤が出勤より小さければ、翌日として 1(=24 時間)を足す」という式です。条件が式に書いてあるので、あとから表を見た人にも意図が伝わりやすくなります。

比べる点MOD 方式IF 方式
式の長さ短いやや長い
意図の読みやすさ仕組みを知らないと読みにくい条件がそのまま読める
24 時間ちょうどの勤務0:00 になる0:00 になる

結果はどちらも同じなので、表を引き継ぐ人が読みやすいほうを選ぶといいでしょう。

日付つきで入力しているなら単純な引き算でよい

出勤と退勤を「2024/11/2 22:00」のように日付つきで入力しているなら、単純な引き算で合います。日付の分だけ退勤のシリアル値が大きくなるからです。

B3: 2024/11/2 22:00
C3: 2024/11/3 6:00
E3: =C3-B3-D3  → 7:00

打刻データを日付つきで出力できる勤怠システムなら、この形で取り込むのがいちばん手間が少なくて済みます。

時間差を時間数・分にすると合わない

給与計算では「8:00」ではなく「8」(時間数)が欲しいですよね。ここでつまずくポイントは 3 つあります。

×24 しても書式が時刻のまま

直す前(I2 が時刻の書式)
I2: =E2*24
→ 0:00 のように表示されることがある

直した後(I2 の表示形式を「標準」に変更)
I2: =E2*24
→ 8

8:00 の中身は 1/3(0.3333…)なので、×24 でちょうど 8 になる計算です。ところが結果セルが時刻の書式だと、8 を「8 日と 0 時間」と読んでしまいます。時刻の部分だけが表示され、0:00 に見えることがあるんです。

式は合っているので、表示形式を「標準」か「数値」にすれば解決です。

端数のずれは ROUND で整える(×1440 の分も同様)

2 行目の退勤が 18:20 だったとします。E2 は 8:20 で、時間数にすると割り切れません。

直す前
I2: =E2*24
→ 8.333333(8 時間 20 分 = 8 と 1/3 時間)

直した後
I2: =ROUND(E2*24,2)    → 8.33(時間数で出す場合)
I2: =ROUND(E2*1440,0)  → 500(分で出す場合)

分に直す ×1440 は、画面上は整数に見えても、内部の計算でごくわずかな端数が出ることがあります。そのまま比較したり切り捨てたりすると、1 分ずれる原因に。

時間数も分も、ROUND で丸めてから使うのが安全です。小数第何位で丸めるかは、社内の給与計算のルールに合わせてください。

休憩の引き算の順序

日勤なら、=C2-B2-D2 と =(C2-B2)-D2 の結果は同じ 8:00 です。日をまたぐ勤務では、休憩を引く前に「翌日」を補えているかが分かれ目。

NG: E3: =C3-B3-D3            → ########
OK: E3: =MOD(C3-B3,1)-D3     → 7:00
OK: E3: =MOD(C3-B3-D3,1)     → 7:00

MOD で翌日を補ってから休憩を引くか、休憩まで引いた差をまとめて MOD に入れます。どちらでも 7:00 になります。

もう 1 つの落とし穴は、休憩の単位です。D2 に「1:00」ではなく「1」と入力すると、Excel は「1 日(24 時間)」として引いてしまいます。

直す前(D2 に「1」と入力)
E2: =C2-B2-D2       → ########

直した後
E2: =C2-B2-D2/24    → 8:00(D2 が時間数のとき)
E2: =C2-B2-D2/1440  → 8:00(D2 に「60」と分で入れたとき)

いちばん簡単なのは、休憩を「1:00」と時刻で入力し直すこと。休憩の列が数値で届く場合だけ、/24 や /1440 で単位をそろえましょう。

まとめ・よくある質問

症状別チェックの流れ

引き算がおかしいと感じたら、次の順に確認すると原因を絞り込めます。

  1. 結果のセルを「標準」にして、中身の数値を見る
  2. マイナスの小数なら、マイナスの時刻か日をまたぐ勤務(MOD か IF で直す)
  3. 14 のような日数が出たなら、書式の問題(標準のままにする)
  4. #VALUE! なら、ISNUMBER で文字列の日付を探す
  5. 値が変わらないなら、計算方法とセルの書式を確認する
  6. 時間数・分にするなら、表示形式を標準にして ROUND で丸める

よくある質問

Q. 1 か月の勤務時間を合計したら、24 時間を超えた分が消えます。

A. 合計が 24 時間を超えると、表示形式「h:mm」では日の部分が表示されません。表示形式を「[h]:mm」に変える必要があります。手順はExcelで時間の合計が24時間を超えると0時に戻る問題の解決法で解説しています。

Q. 締め期間の日数が 1 日少なく出ます。

A. =K2-J2 は「何日後か」を求める式なので、開始日を数に含みません。開始日と締め日の両方を数えるなら、=K2-J2+1 で 15 日になります。

Q. 日数を関数で出したり、年・月単位で期間を出したりしたいです。

A. 日数だけなら、=DAYS(K2,J2) でも 14 になります(DAYS関数の使い方)。勤続年数のように年・月単位で数えたいときは、DATEDIF関数の使い方を参考にしてください。

日付も時刻も、Excel の中ではただの数値です。おかしいと感じたら、まず「標準」にして中身を見る。この 1 ステップが、原因を絞り込むいちばんの近道です。

冒頭の早見表をブックマークしておけば、次につまずいたときもすぐ原因にたどり着けますよ。

タイトルとURLをコピーしました