【Excel関数】TIMEVALUEが24時間以上で誤る原因と対処

勤怠データの月間労働時間が「171:00」のような文字列で出力されていて、計算に使える数値に変えたい。そんなときにTIMEVALUE関数を使うと、エラーにならないまま誤った値が返ってきます。

この記事では、TIMEVALUEが24時間以上の値で誤る原因と、累計時間を正しく変換する方法を紹介します。数値はすべて、実際にExcelで確かめた結果です。

こんな方に向けた記事です

TIMEVALUEに24時間以上の値を渡すとどうなるか

TIMEVALUEは、時刻を表す文字列をシリアル値（1日を1とする数値）に変換する関数です。1日以内の時刻なら、問題なく変換できます。

=TIMEVALUE("08:30")   → 0.354166667

ところが、24時間を超える値を渡すと結果が変わります。次の表は、同じ文字列を3通りの方法で変換した結果です。

元の文字列	TIMEVALUE	VALUE	本来の値（時間）
08:30	0.354166667	0.354166667	8.5
25:00	0.041666667	1.041666667	25
171:00	0.125	7.125	171
2000:30	0.354166667	83.35416667	2000.5

「171:00」は、本来なら171時間（シリアル値では7.125）です。しかしTIMEVALUEは0.125、つまり3時間分しか返しません。「2000:30」も、返ってくるのは8時間30分分の値です。

#VALUE!などのエラーは出ません。値が小さいだけなので、月末の集計が合わなくなるまで気づけないのが厄介な点です。

TIMEVALUEは「時刻」を扱う関数で、「経過時間」を扱う関数ではありません。Microsoftの公式説明でも、戻り値は0〜0.99988426（0:00:00〜23:59:59）の範囲とされています。

この範囲を超える値を渡すと、24時間分が捨てられ、1日未満の端数だけが返ります。実測でも、次のとおり24で割った余りになっていました。

累計時間は経過時間なので、時刻用のTIMEVALUEとは用途が合いません。

いちばん簡単なのは、TIMEVALUEをVALUEに置き換える方法です。

=VALUE(A2)

先ほどの表のとおり、「171:00」は7.125、「2000:30」は83.35416667と、正しい値になります。秒が付いた文字列にも対応でき、「171:00:30」は7.12534722…になりました。

「:」の前後で時間と分を取り出し、自分で換算する方法もあります。

=VALUE(LEFT(A2,FIND(":",A2)-1))/24 + VALUE(MID(A2,FIND(":",A2)+1,2))/1440

結果はVALUEと同じです。違いは秒の扱いで、この式は時間と分だけを見るため、「171:00:30」でも7.125になります（秒は無視されます）。秒を切り捨てたいときは、こちらが向いています。

分の取り出しを2文字で固定しているので、「hh:mm」形式の文字列が前提です。

変換後の値はシリアル値なので、そのままでは「7.125」のように表示されます。セルの表示形式を[h]:mmにすると、「171:00」の形で表示できます。実測では、25:00は「25:00」、2000:30は「2000:30」と表示されました（画像のE列）。

変換後の値は数値なので、合計や時間数への換算にも使えます。

=SUM(C2:C5)     ← 変換後の値の合計（表示形式は[h]:mm）
=C4*24          ← 時間数に換算（7.125 → 171）

時刻や日付がシリアル値という数字で保存されている仕組みは、日付が数字になる仕組みとVBA・PQでの扱いで整理しています。

TIMEVALUEに数値を渡している：セルにすでに数値（0.5など）が入っている場合、TIMEVALUEは#VALUE!になります。TIMEVALUEは文字列を渡す関数です。
秒付きのデータにLEFT+MIDを使う：秒が無視されます。秒まで必要なら、VALUEを使います。
表示形式が「標準」のまま：値は合っていても「7.125」と表示され、誤りに見えます。表示形式を[h]:mmに変えます。

#VALUE! が出たときに最初に確認する場所は、【Excel関数】#N/A・#VALUE!・#REF!が出たら最初に確認にまとめています。