時間計算

1. Excelでの時間の扱われ方
   • ◆入力した時間文字列≠確定後の内容
   • ◆シリアル値
   • ◆表示形式との関係
   • ◆
2. 時間計算で発生する問題
   • ◆出勤時間を15分単位で切り上げるには？
   • ◆退勤時間を15分単位で切り下げるには？
   • ◆２４時間以上の集計ができない
   • ◆時間の比較がうまくできない
   • ◆２４時をまたいだ時間計算
   • ◆時間計算で誤差が出る

Excelでの時間の扱われ方

◆入力した時間文字列≠確定後の内容

次に挙げる表を例にします。
	A	B
1	B2への入力	18:30
2	セルの表示	18:30
3	B2の数式バー表示	06:30:00 PM
4	=LEFT(B2,100)　→	0.770833333333333

上の表から次のようなことが分かります。
• 数式バーでの表示は「06:30:00 PM」や「18:30:00」のような、セルとは違った表示がなされる。
  （数式バーでの表示はWindowsのコントロールパネルの時刻表示設定に影響される）
• 　=LEFT(B2,100)　で「18:30」や「06:30:00 PM」の部分だけ取り出そうとしても 「0.770833333333333」という値しか取り出せない。
ということは、入力した時間文字列は、確定後にまったく別の値、表示形式に変質しているということです。 書式設定「G/標準」のセルへ「：（セミコロン）」や「　時　分　秒」の時刻を表す文字列と 数字の組み合わせが入力され、時刻を表現する文字列として有効であると判断された場合に セルの内容は次のように変更されます。
• 入力された「文字列」を時刻を表す「シリアル値」へ
• 表示形式を「時刻」の中から入力された文字列になるべく近いものへ

▲PAGETOP

◆シリアル値

シリアル値とは次のような数値です。
• ひとつの数値で日付と時刻を同時に表現するものである
• 1900年1月1日 0:00:00を　1　として起算する（Windows）
• 整数部分は　1日　経過ごとに　1　増加する、日付に対する連続値である
• 時刻は　日付と次の日付の間の値と言う意味で小数部分にて表現する。
数値を「日付と時刻を表現する数値として扱う」場合に「シリアル値」と呼びます。 日付や時刻の表示されているセルの表示形式を「G/標準」や「数値」に変更すれば、 数値として表示されます。 また数値なので計算ができます。 日付部分を含まない、時刻だけの扱いに関しては次のような特徴があります。
• 　0　を　0:00　、　1　を　24:00　として扱う
• 1時間増加すると　1/24　増加する
• 1分増加すると　1/1440　増加する
• 1秒増加すると　1/86400　増加する

▲PAGETOP

◆表示形式との関係

シリアル値の実体は数値でしかありませんが、 表示形式「日付」「時刻」の表示範囲により表示制約を受けます。 表示できない範囲の数値はエラー表示「#####」になります。
• 　0　〜　2958465.99998843 までの範囲の数値
• マイナスの数値は日付、時刻として表示できない
• 1900年1月1日より前の日付は表示できない（Windows）

▲PAGETOP

時間計算で発生する問題

◆出勤時間を15分単位で切り上げるには？

特定分数単位で切り上げる、という計算は良く使われると思います。しかし、 時刻というものが、10進数の数値でないために単純にROUNDUP関数では切上げが出来ません。 このような場合は一定値間隔で数値を丸める、CEILING関数が役に立ちます。

	A	B
1	打刻時間	08:35
2	出勤時間	08:45	←=CEILING(B2,"0:15")

関数ヘルプ
CEILING関数(HINT)


▲PAGETOP

◆退勤時間を15分単位で切り下げるには？

特定分数単位で切り下げる、という計算も良く使われると思います。しかし、切り上げの時と同様 時刻というものが、10進数の数値でないために単純にROUNDDOWN関数では切捨てが出来ません。 このような場合は一定値間隔で数値を丸める、FLOOR関数が役に立ちます。

	A	B
1	打刻時間	16:35
2	退勤時間	16:30	←=FLOOR(B2,"0:15")

関数ヘルプ
FLOOR関数(HINT)


▲PAGETOP

◆２４時間以上の集計ができない

問題点

時間の計算で、合計が24時間を超える場合に正しい合計値が表示されない。

	A	B
1	時間1	07:30
2	時間2	08:30
3	時間3	09:30
4	時間4	10:30
5	合計	12:00


上記表のB5セルではSUM(B1:B4)が入力されています。 計算上は36時間なのでB5には36:00が得られるはずですが、結果は12:00と表示されます。

原因

「計算がうまくできていない」のではありません。 表示形式を「G/標準」に設定すると「1.5」が表示されます。 これは「1.5日」つまり、36時間を示すシリアル値なので計算はできています。 単に合計するだけでは正しい時刻表示がえられません。 表示形式「時刻」の設定では整数部分は「日数」と捉えられるため、 小数点以下の部分「0.5」だけが時刻表示の対象となってしまいます。

解決方法

表示形式ユーザー定義で 書式文字列を[h]:mmとします。[]で囲むことで 本来日数部分として計算される整数部分を時間換算して表示できるようになります。

▲PAGETOP

◆時間の比較がうまくできない

時間の計算で「計算結果がある時間に達しているかどうか」を比較することは良くあると思いますが、 時刻を表すの数値の特性からその比較が意図どおりにならない場合が発生します。 原因は何種類か考えられますが、以下はそのひとつ、「比較対照の質の違いの問題」。

問題点

時間の比較がうまく働かない。

	A	B
1	出勤時間	08:30
2	退勤時間	17:00
3	休憩時間	0:30	←=IF(B2-B1>="7:00","1:30","0:30")
4	実働時間	08:00	←=B2-B1-B3


上記の表B3では退勤時間−出勤時間によって表示する休憩時間を条件分岐することを 意図していますが、常に「0:30」が返ってしまいます。

原因

「B2-B1>="7:00"」の比較がうまく働かず、常に「FALSE（偽）」が返ってしまいます。 「B2-B1」の部分は数値。「"7:00"」の部分は文字列。 数値（シリアル値）と文字列との比較なので、常に「違うもの」と判断されてしまいます。

解決方法

数値（シリアル値）と文字列の比較、つまり、質の違うもの同士の比較だから 「違うもの」と判断されるのは当然。ならば、 同質のものに変換してから比較すればよいのです。
【数式例】

	=IF(TEXT(B2-B1,"h:mm")>="7:00","1:30","0:30")
	

「B2-B1」の計算結果をTEXT関数で時刻を表す文字列に変換して比較しています。
別の方法として
=IF(B2-B1>=TIMEVALUE("7:00"),"1:30","0:30")
というシリアル値同士の比較を行う数式も考えられますが、 「B2-B1」の部分で「◆時間計算がうまくできない（３）」に述べる 微小な誤差の発生する場合があるので、 TEXT関数による誤差吸収の方法が上策と私は考えます。

関数ヘルプ
IF関数(HINT)
TEXT関数(HINT)
TIMEVALUE関数(HINT)


▲PAGETOP

◆２４時をまたいだ時間計算

就労時間が24:00をまたぐ場合の時間計算はよく問題として挙げられます。

問題点

24:00をまたぐ時間計算はどうしたらよいか？

	A	B
1	開始時刻	23:30
2	終了時刻	08:00
3	経過時間	##########	←=B2-B1




原因

開始時刻が終了時刻よりも大きいために単純に時刻の差し引きを行うとエラー表示「#####」になります。

解決方法

「表示形式が日付・時刻でありマイナスの値にセルに格納されている」場合の表示上の特殊なエラーであり、 値としてエラーが返っているわけではありません。マイナスシリアル値はありえない、というExcel設計仕様です。 計算上のシリアル値がマイナスになってしまうのは単に時計表示上の終了時刻を入力するため。 終了時刻が開始時刻よりも小さいからといってExcelは「これは次の日の時刻だな」とは決して解釈してくれません。 終了時刻が開始時刻よりも大きなシリアル値になるような入力や計算の仕組みを作る必要があります。

日付も管理する

24:00をまたぐような時間計算、もしくは24時間を超えるような時間計算が発生する場合には 時刻データだけでなく、日付も計算のデータとして利用することをお勧めします。 計算に日付を含めることによって終了時刻を開始時刻より常に大きくすることができます。 また、24時間を超える場合の時間計算にも対応することができます。

	A	B	C
1	開始時刻	2005/1/30	23:30
2	終了時刻	2005/1/31	08:00
3	経過時間		8:30	=(B2+C2)-(B1+C1)


経過時間の表示を24時間以上にするには表示形式ユーザー定義で 書式文字列を[h]:mmとします。

終了時刻の入力を工夫する

日付と時間を常にセットで管理するのは確実な方法ではありますが、 「いちいち日付まで入力するのは面倒」。そこで、終了時刻の入力を工夫します。
日付をまたぐ場合は24時間を加えた時刻を入力することでたいていの時間計算は解決します。

	A	B
1	開始時刻	23:30
2	終了時刻	32:00
3	経過時間	8:30	=B2-B1


経過時間の表示を24時間以上にするには表示形式ユーザー定義で 書式文字列を[h]:mmとします。

計算式を工夫する

それでもなお、「いちいち24:00を加えた時間を暗算するのは面倒」とおっしゃる御仁のために、 計算式を工夫しておくことで時刻入力が時計表示通りでも可能ではあります。
ただしこの方法は経過時間が24時間を超えない場合に限り有効です。

	A	B
1	開始時刻	23:30
2	終了時刻	08:00
3	経過時間	8:30	=TIMEVALUE(TEXT((1+B2)-B1,"h:mm"))


終了時刻に最初から「１(24時間)」を加えておくことで計算がマイナスになってしまうことはありません。 終了時刻が同日の場合でもTEXT関数で時刻部分だけを取り出す形になるので計算結果の上では矛盾は出ません。 逆に24時間を超える計算はできないのでその点は注意して使用すべきです。
【数式例】次のような数式でTIMEVALUE関数の代替をすることが可能です。

	=--TEXT((1+B2)-B1,"h:mm")
	

関数ヘルプ
TEXT関数(HINT)
TIMEVALUE関数(HINT)


▲PAGETOP

◆時間計算で誤差

時間計算をしていると、微妙に計算が合わないケースが発生します。 計算式が理屈上正確でも計算が合わなくなるので、 気をつけていないと うっかり見逃してしまう場合があります。 この問題は特に賃金計算にかかわる方には大問題です。

問題点

簡単な例で誤差の出るケースを紹介します。

【例】出勤時間、退勤時間、休憩時間から残業時間を求める。

	A	B	C	D	E
1	出勤	退勤	休憩	実労時間	残業時間
2	7:00	17:30	2:00	=B2-A2-C2	=FLOOR(D2-TIMEVALUE("7:45"),TIMEVALUE("0:15"))




計算上の要件

規定労働時間	７時間４５分
実労時間	勤務時間 - 休憩時間 - 退勤時間
残業時間	実労時間 - 規定労働時間（１５分単位で切捨て）


のようになっているとき、例示の表のような数値、式を入力すると次のような結果になります。

	A	B	C	D	E
1	出勤	退勤	休憩	実労時間	残業時間
2	7:00	17:30	2:00	8:30	0:30


D2の実労時間は8:30。これはあっています。
問題は最終的に求めたい残業時間で、「8:30-7:45」ですから0:45。 これを15分単位で切捨てなのですが、0:45はちょうど15分単位なのでそのまま0:45が返されるはずです。 ところが、この計算では0:45と表示されるべきところが、0:30になってしまいます。
E2に入力されている式「=FLOOR(D2-TIMEVALUE("7:45"),TIMEVALUE("0:15"))」は計算式の組立としては間違っていません。
では、なぜこのような誤差が発生してしまうのでしょうか。

原因

この問題はシリアル値の特性そのものが原因です。 時刻を表すシリアル値は「24時間」が「１」となるような「小数」部分で表現されます。 しかるに、「１」は「２４」で割り切れません。割り切れないため小数点以下の桁が延々と続きます。 ところが、パソコンの内部で扱える小数点以下の桁数はある程度限界が設けられています。 その限度内で計算をするため、割り切れない小数はあくまで「誤差を含んだ近似値」として扱われます。
ということは次のようなことが言えます。

• 「近似値」と「近似値」を計算した場合にはその誤差が拡大する
• 「近似値」であるために「等差｣で増加しない

先の例に挙げたものは、次のような原理でこの15分の誤差が発生してしまいます。



1	「D2-TIMEVALUE("7:45")」の部分が小数同志の計算を行なう
2	「0.0312499999999999」がその結果（「0:45」のシリアル値「0.03125」）
3	この値をFLOOR関数で15分単位で丸める
4	「0:45」に「0.0000000000000001」だけ不足しているために0:30が返る


例としては荒削りなので不適切かもしれませんが、このように正確な分刻みの数値にはならない場合があると言うことです。 これはExcelで扱う時刻を表す小数値がが近似値であることに起因していて、 機械的には解決のしようのない問題なのだそうです。
（らしいです。残念ながらこの点の説明は、私の能力が及びません。以下のMicroSoftの技術情報をご参照下さい。）


参考
[XL] 浮動小数点の計算誤差と IEEE フォ-マット
[XL97]特定の小数部を持つ数値を入力した場合に誤差を生じる
[XL97]浮動小数点数値演算の結果に含まれる丸め誤差について

実務上、これはたいへんやっかいな問題です。せっかくの表計算ソフトの 計算がアテにならないのでは大変困りモノです。 先に述べているように、計算方法は決して間違っていないので、数式の中で扱う「数値の質」を 何とかする必要があります。

解決方法

整数で計算

「小数同士の計算で誤差が出るのであれば、整数にしてしまえばよい」
分単位の計算ならば、時刻シリアル値（小数）を分単位の整数に換算することで誤差を吸収することができます。

【修正例】

	A	B	C	D	E
1	出勤	退勤	休憩	実労時間	残業時間
2	7:00	17:30	2:00	=B2-A2-C2	=FLOOR(ROUND(D2*1440-TIMEVALUE("7:45")*1440,0),15)/1440




1	ROUND(D2*1440-TIMEVALUE("7:45")*1440,0) D2と"7:45"のシリアル値にそれぞれ1440（24時間×60分）を乗算することで分単位の整数になります。 誤差対策として、ROUND関数で整数値に丸めます。 差し引くと規定労働時間外の分単位の数値が返ります。
2	FLOOR(〜,15) FLOOR関数で 1 の数値を15分単位に切捨て（丸め）ます。 この例では「45」と言う整数がかえります。
3	〜/1440 最後に1440で割って小数（シリアル値）に戻します。


このようにシリアル値を計算したい単位での整数同士の計算とすることで、 誤差を吸収することができます。

いったん時刻文字列に

【修正例】

	A	B	C	D	E
1	出勤	退勤	休憩	実労時間	残業時間
2	7:00	17:30	2:00	=B2-A2-C2	=FLOOR(TEXT(D2-TIMEVALUE("7:45"),"h:mm"),TIMEVALUE("0:15"))




1	TEXT(D2-TIMEVALUE("7:45"),"h:mm") TEXT関数によりD2-TIMEVALUE("7:45")の計算値を時刻の文字列に変換します。 例の計算では"0:45"という文字列が返ります。
2	FLOOR(〜,TIMEVALUE("0:15")) FLOOR関数で 1 の文字列をTIMEVALUE("0:15")のシリアル値で切捨て（丸め）ます。 この例では正確に"0:45"に対応するシリアル値がかえります。


このようにシリアル値をいったん時刻の文字列にすることで、 誤差を吸収することができます。 このことから逆に、時刻文字列から得られるシリアル値は常に規定のシリアル値であることが分かります。

関数ヘルプ
CEILING関数(HINT)
FLOOR関数(HINT)
TIMEVALUE関数(HINT)
TEXT関数(HINT)


▲PAGETOP

時刻を午前（午後）○○時○○分と表示

セルの書式設定→表示形式で午前（午後）○○時○○分という日本語表示は不可能でないにしても いろいろ制限があります。


参考
表示形式の設定例・応用例-日付、時間の表示
時刻を○○時○○分　午前（午後）と表示(Excel 2002以降)


不適切な表示にしてしまうよりは別のセルを使って関数で午前／午後を求めたほうが確実ではあります。


問題点

入力された時刻を「午前/正午/午後」の表示にしたい

関数例

セルA1にシリアル値が入力されていて、他のセルへテキストとして出力する場合 =IF(--TEXT(A1,"hh:mm")=0.5,"正午", TEXT( IF(HOUR(A1)>=13,A1-0.5,A1), IF(HOUR(A1)>=12,"午後","午前")&"hh時mm分"))
他にも数式は考えられますが、シリアル値の時刻部分が正午より小さい/等しい/大きい という条件分岐が基本になります。
表示形式のAM/PM表示を利用して分岐するには下記。
=SUBSTITUTE(SUBSTITUTE(TEXT(A1,"AM/PMhh時mm分"),"AM","午前"),"PM","午後")


関数ヘルプ
HOUR関数(HINT)
TEXT関数(HINT)
SUBSTITUTE関数(HINT)


▲PAGETOP