エクセル関数応用
時間計算で困ったときの確実な対処方法

Excel関数の解説、関数サンプルと高等テクニック
最終更新日:2021-02-02

時間計算で困ったときの確実な対処方法


Excelで非常に多い質問の一つが時間計算です。


日付・時刻はエクセルのデータ形式の一つですが、
特に時間計算は面倒で対処方法も書籍・ネットでもあまりみかけません。

日付に関しては便利な関数も用意されていますし、
なにより、1日が数値の1(これをシリアル値と言います)で表されているので、大抵はなんとかなっているようです。

対して、時刻のシリアル値は小数以下になっていて、かつ、関数もあまり用意されていない為にどうしてよいか分からなくなってしまうことが多いのでしょう。

以下で、確実に時間計算を行う鉄板の方法を紹介します。
まずは、その前に、事前知識が必要になります。
ちょっと難しい話になりますが、さくっと読み流してみてください。


日付・時刻のシリアル値とは

日付は、1900/1/1を1として、そこからの経過日数であらわされます。
つまり、日付のシリアル値とは、1900/1/1の何日後かの数値ということになります。
雑学話としては、「エクセル雑感.エクセルの日付のお話」をご覧ください。
エクセルでの日付の扱いは特別です。では、エクセルでの日付についてのお話です。エクセルでの日付は1900/1/1を1として、そこからの日数になっています。通常、私もこのように説明するのですが、実は、ちょっとからくりがあります。
1日が1なので、通常の足し引き計算はなんら問題がありませんので、あまり意識することはないと思います。
年月の表記においては各種問題もありますが、これらに対する関数は豊富に存在しています。

時刻は、1日(24時間)を1として、その分数(小数)としてあらわされます。
つまり、1時は1/24、2時は2/24です。
同様に分は、さらに1/60となります。
つまり、1分は1/24/60、2分は2/24/60です。

ですので、2014/5/6 14:30は、
1900/1/1の41765日後なので、=41765+14/24+30/24/60=41765.6041666667
となります。
そして、Excelは整数部分を日付、小数以下を時刻として認識します。

従って、通常の書式「hh」では小数部分の時刻のみを取り出します。
整数部分の日付も含めて時刻に(つまり24hを超えて表示)する場合は、
「[hh]」といった書式が必要になります。

ここで、日付・時刻とは、特に時刻とは、時計の針が指すその時刻になります。
決して時間(時刻と時刻の間隔)ではないということです。
したがって、マイナスは決して扱えません、この事を理解していない場合が非常に多く見受けられます。

シリアル値を日付・時刻の表示形式にする各種指定方法は、「エクセル基本操作.日付、時刻に関するあれこれ
1.曜日の表示方法 2.数値を日付にする方法 3.24時以降(26時等)の表示書式 4.日付と日付の間隔(年齢等)の求め方、DATEDIFに関して 5.日付、時刻のシリアル値(数値) 6.和暦表示について「元年」表記」1.曜日の表示方法 曜日の書式に関しては、「セルの書式設定」にサンプルが出ていない為、
個別の関数等については、以下を参考にしてください。

Excelにおける小数の問題

Excelに限ったことではなく、PC、いやコンピューター全般の問題点として小数の表現方法が存在します。
詳しい解説は、ここでは省きますが、
コンピューターが全て2進数で表現されているため、表現できない小数点数があったり、
計算過程で誤差が生じてしまうというものです。
VBAの小数以下の演算誤差について
ワークシートで時間計算をしていると、時に不可解な結果に見舞われることがあります。エクセルの時間は、1日を1とした数値で管理されているため、1時間は1の24分の1の数値になります。この小数以下の計算において、エクセルが正しく計算できないというものです。

これらの事は、Microsoftの各種ページでも解説されていますので、興味があれば読んでみてください。
Excel で浮動小数点演算の結果が正しくない場合がある
浮動小数点演算で丸め誤差を修正する方法

ここでは、この問題により起こる時間計算の誤差の対処方法を記します。

さて、いよいよ本題に入ります。
よくある(かどうかは別にして)出退勤の計算を例に話を進めます。


どんな時に問題が発生するか

・出勤時刻
・退勤時刻
・休憩時間
・所定時間

ここから、遅刻・早退・残業時間を求める場合です。
まあ遅刻・早退は、望ましくないので(笑)、残業時間の算出を例にとりましょう。

・出勤時刻 8:15
・退勤時刻 17:15
・休憩時間 1:00
・所定時間 8:00

残業時間=17:15-7:15-1:00:8:00=0:00
シート上で計算してみてください、正しく計算できています。

出勤時刻 退勤時刻 休憩時間 所定時間 残業時間
8:15 17:15 1:00 8:00 0:00

そう、普通は何も問題ないように思えます。
では、その下に以下のようにいれて、さらにドラッグコピーをします。

出勤時刻 退勤時刻 休憩時間 所定時間 残業時間
8:15 17:15 1:00 8:00 0:00
8:16 17:16 1:00 8:00 0:00

この2行分のセル範囲を選択しドラッグコピーすると、

出勤時刻 退勤時刻 休憩時間 所定時間 残業時間
8:15 17:15 1:00 8:00 0:00
8:16 17:16 1:00 8:00 0:00
8:17 17:17 1:00 8:00 0:00
8:18 17:18 1:00 8:00 0:00
8:19 17:19 1:00 8:00 0:00
8:20 17:20 1:00 8:00 0:00
8:21 17:21 1:00 8:00 0:00
8:22 17:22 1:00 8:00 0:00
8:23 17:23 1:00 8:00 0:00
8:24 17:24 1:00 8:00 0:00
8:25 17:25 1:00 8:00 ########
8:26 17:26 1:00 8:00 0:00
8:27 17:27 1:00 8:00 ########
8:28 17:28 1:00 8:00 0:00
8:29 17:29 1:00 8:00 ########
8:30 17:30 1:00 8:00 ########

8:25のところから、なにやらおかしくなっています。
手入力で、8:25と入れなおすと、正しく0:00となります。
これは、ドラッグコピーで作成された時刻が、手入力の時刻とわずかに違いがあるということです。
手入力する前の8:25のシリアル値は、0.350694444444445
手入力した後の8:25のシリアル値は、0.350694444444444
わずかに、最後で違いがあります。
この違いは、秒まで表示しても時刻形式では差異が見て取れません。

つまり、いくら眺めていても、この問題は解決しませんし、
全て手入力し直していたのでは、何のためのExcelだかわからなまなってしまいます。
この問題は、何もドラッグコピー時だけの問題ではなく、
何らかの計算結果を時刻表示しているときには、常に付きまとう問題となっています。
ここでは、簡単に再現でき、かつ、理解しやすいようにドラッグコピーで説明しました。

分までなら、あまり計算誤差は発生しませんが、秒まで計算すると頻発します。

開始時刻 0:08:25 0:23:43
終了時刻 0:17:25 0:32:43
経過時間 0:09:00 0:09:00 FALSE

右下のFALSEは、2つ左の0:09:00と、1つ左の0:09:00をEXACT関数で比較した結果です。
同じ数値ではないということになります。
秒までの計算式では、むしろ一致することの方が少ないくらいに違いが発生します。

では、本当の本題です。
このような問題を解決するための確実な方法です。


確実な時間計算方法

計算をExcelに任せて、小数点数の2進数で計算するから問題なのです。
したがって、要は人間が頭のなかで行っているのと同様に、10進計算すればよいのです。
そのためには、まずは10進計算できる数値に変換してから計算します。
つまり、小数点以下のシリアル値ではなく、整数としての独自シリアル値として扱うのです。
話を分かりやすくするために、分単位で話を進めます。

見た目で、
8:25
とあるなら、これをそのまま1分を1とした数値に変換します。
つまり、8*60+25=505にします。
その方法としては、何通りかありますが、
元のシリアル値が、1日=24時間=1であることから、
A1セルに8:25が入っているとして、
A1*60*24=505となります。
※計算結果のセルは表示形式を標準に変更してください。
*24で1時間が1となり、*60で1分が1となります。
また、
少しややこしくなりますが、基本単位で割れば、基本単位を1とした数値になりますので、

A1/Time(0, 1, 0)=505
これは、
8:25/(24/60)=8:25*60*24=505

両者は同じことになります。

さて、これで一見よさそうにみえますし、まずは問題は無いでしょう。
しかし、小数以下の誤差が問題であるといっているのに、
この計算式では、やはり誤差が出てしまうのではないかと不安があります。
※この計算式で誤差がでてしまうかどうかの検証はしていません、
多分、ほとんど誤差が出ることはないと思うのですが、全ての数値で検証できませんので。
さらには、実際のセル値が、
8:25:58
のように秒数が入ってしまっていて、見た目が8:25となっている場合は、
結果が506となってしまいます。
もちろん、小数以下を表示すれば、
505.983333333
このような数値となっているわけです。

では、人間の見た目通りの計算をしたい場合は、どうしたら良いかです。
それには、Excelで用意されている関数を使います。

HOUR(A1)*60+MINUTE(A1)=505

この計算式なら、実際は8:25:59とセルにはいっていて、見た目だけ8:25となっていても問題ありません。
また、こんな計算方法もあります。

TEXT(G18,"hh")*60+RIGHT(TEXT(G18,"hhmm"),2)

計算式の意味としては理解しやすいかもしれませんが、やはり前者の計算式の方が良いと感じます。
ちなみに、TEXT関数で分だけ取り出そうとして、"mm"の指定は月を取り出してしまいます。
(このTEXT関数で、分のみ指定する方法が無いのは、かなり痛い仕様と言えます)

さて、この1分を1とした数値に変換できたら、後は普通に足し引きして問題ないことは説明が不要でしょう。
でも、最後は、分かりやすいように時間表示にしたくなります。
計算結果が、75、これでは分かりずらい、やはり、1:15のように表示しておきたいところです。
それには、既に登場している、TIME関数を使えば一発で済みます。
B1セルに1分を1とした数値に変換後の計算結果が入っているとして、

TIME(0, B1, 0)

これで、時刻表示に自動変換されます。
TIME関数の引数には、60を超える分や秒を指定しても、正しく変換してくれます。

これで、全ての時間計算に関する問題は解決します。
ちなみに、秒で計算する場合は、

HOUR(A1)*60*60+MINUTE(A1)*60+SECOND(A1)=30359
TIME(0, 0, B1)


このようにすれば良い事になります。

実際には、実務での残業時間計算で1分単位で時間計算することは稀でしょう。
(労基署の言い分としては、1分単位でやりなさいとなりますが、その辺は専門家に委ねるとして)

例えば15分単位にする場合、
出勤時刻は15分毎に切り上げ、つまり、8:25は8:30
退勤時刻は15分単位に切り捨て、つまり、17:35は17:30
このようにしてから、残業時間を計算するというのは昔からよくあることです。
(これで労基署が許してくれるかは別の話ですよ)
このような場合は、

CEILING
FLOOR

これらの関数を使って処理してから、足し引き計算をすることになります。
ちなみに、出勤時刻は15分毎に切り上げた分単位にする場合は、
CEILING(HOUR(A1)*60+MINUTE(A1),15)
このようになります。
元の時刻を15分単位にしてからなら、
CEILING(A1,TIME(0,15,0))
としてから、分に変換しても良いですね。


TIME関数の制限について

TIME関数の引数は、
ヘルプにも書かれていますが、「0~32767の範囲で指定します。」
つまり、秒数なら概ね9時間以上となる場合は制限値を超えてしまいます。
そのような場合は、時と合わせて計算する工夫が必要になります。

A1セルに秒数が入っているとして、
単純に、
=TIME(0,0,A1)
とすると、9時間を超える秒数の場合は、#NUMとなってしまいます。
そこで、
A2=FLOOR(A1,60*60)
これは、数値から、時間部分だけを取り出します。
そして、
A3=A1-A2
これは、時間を引いているので、残りの秒数になります。
上記をTIME関数に入れて、
=TIME(A2/60/60,0,A3)
これで計算できます。
(途中の計算方法は、色々な関数の使い方があります)

また、そもそも、TIME関数は24時間を超える値を返しません
24時間以上は、DATE関数の範囲となります。
この場合も、日(24時間)以上は別途取り出す工夫が必要になります。
上記のように、A1セルに秒数が入っているとして、
A2=FLOOR(A1,60*60*24)
これは、数値から、日部分だけを取り出します。
そして、
A3=A1-A2
これは、数値から、残りの24時間未満を計算しています。
=A2/60/60/24
これで、日数に戻せますし、日数に*24で時間、*24*60で分数に戻せます。
24時間未満の部分をTIME関数で処理して、
24時間を超える部分は、別途加算するようにします。
ちなみに、先に書いているように、1日=1ですので、
シリアル値計算としては、日数は単純に加算できます。


単純化した結論

時間計算に限らないのですが、まずはExcelの基本関数をうまく使うことを考えましょう。
質問のほとんどは、基本関数の組み合わせでなんとかなります。
時間計算なら、

TIME
TIMEVALUE
HOUR
MINUTE
SECOND

CEILING
FLOOR


まずは、これらの関数を使って処理できないかを工夫してみてください。
大抵(ほぼ全て)は、これらの関数と、文字列関数の一部を使うだけで解決できます。


いろいろと時間計算が面倒なことはご理解いただけたかと思います。
本当の意味での結論としては、
細かい時間にとらわれない仕事をしたいものだということです。





同じテーマ「エクセル関数応用」の記事

MATCH関数 解説・応用・使用例
選択行の色を変える(条件付き書式,Worksheet_SelectionChange)
他ブックを参照できる関数、他ブックを参照できない関数
時間計算で困ったときの確実な対処方法
VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
SUMIF関数の良くある間違い
論理式とは条件式とは(IF関数,AND関数,OR関数)
先頭の数値、最後の数値を取り出す
最後の空白(や指定文字)以降の文字を取り出す
SUMIFの間違いによるパフォーマンスの低下について
数値範囲で表検索するVLOOKUP近似一致


新着記事NEW ・・・新着記事一覧を見る

還暦のVBA:VBAまでたどりつけるか… (2021-09-29)
VLOOKUPを使うことを基本としてシートを設計すべきか|エクセル雑感(2021-08-17)
コンピューターはブラックボックスで良い|エクセル雑感(2021-08-14)
小文字"abc"を大文字"ABC"に変換する方法|エクセル雑感(2021-08-13)
ADOでテキストデータを集計する|VBAサンプル集(2021-08-04)
VBA学習のお勧めコース|エクセル雑感(2021-08-01)
エクセル馬名ダービー|エクセル雑感(2021-07-21)
在庫を減らせ!毎日棚卸ししろ!|エクセル雑感(2021-07-05)
日付型と通貨型のValueとValue2について|エクセル雑感(2021-06-26)
DXってなんだ? ITと何が違うの?|エクセル雑感(2021-06-24)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.Excelショートカットキー一覧|Excelリファレンス
3.変数宣言のDimとデータ型|VBA入門
4.RangeとCellsの使い方|VBA入門
5.繰り返し処理(For Next)|VBA入門
6.マクロって何?VBAって何?|VBA入門
7.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
8.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
9.セルに文字を入れるとは(Range,Value)|VBA入門
10.並べ替え(Sort)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ