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

Excel関数の解説、関数サンプルと高等テクニック
公開日:2014-05-06 最終更新日:2022-05-29

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


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の小数以下の演算誤差について
・小数以下の演算誤差とは ・小数以下の演算誤差を実際のVBAで確認 ・データ型で小数以下の演算誤差に対応 ・小数以下の演算誤差にデータ型以外で対応する ・VBAの小数以下の演算誤差の最後に

これらの事は、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/(1/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関数 解説・応用・使用例

MATCH関数は、検索ワードで最頻出のひとつです。非常に便利な関数です。少し込み入った事を関数でやろうとした時は、必ず必要になる関数です 機能 セルの範囲内で指定された項目を検索し、その項目の相対的な位置を返します。
選択行の色を変える(条件付き書式,Worksheet_SelectionChange)
クリックまたはカーソルキーで選択セルを移動した場合に、当該行の色を変更して目立たせたせる方法で、条件付き書式と、シートのイベントであるWorksheet_SelectionChangeを使用します。Worksheet_SelectionChangeイベントのみでやろうとすると、直前の選択行の色を元に戻す必要がある為、
他ブックを参照できる関数、他ブックを参照できない関数
・他ブックを参照できる関数、他ブックを参照できない関数 ・SUMPRODUCT関数:後日追記 ・他ブックを参照できる関数、他ブックを参照できない関数のまとめ ・テーブル構造化参照では他ブックは取得できません ・他ブックを参照する最も簡単な方法 ・配列数式を使って、他ブックを参照する ・他ブックを参照することについて
時間計算で困ったときの確実な対処方法
VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
・VLOOKUP関数 ・キー列より左側の列を取得したい ・MATCH関数 ・INDEX関数 ・OFFSET関数 ・MATCH関数とINDEX関数を使う ・MATCH関数とOFFSET関数を使う ・キー列より左側の列を取得のまとめ ・配列を使いVLOOKUPでキー列より左側の列を取得
SUMIF関数の良くある間違い
エクセルの関数の中で最も頻繁に使われる関数と言っても過言ではないSUMIF関数ですが、間違った指定をして、合計が合わずに悩み続けて時間を浪費してしまうことあります、そういう間違いで最も多いのが、範囲と合計範囲の指定間違いです。まずは、SUMIF関数の確認 SUMIF関数 範囲の中で、指定した条件を満たすセルの値を合…
論理式とは条件式とは(IF関数,AND関数,OR関数)
エクセルを使いこなす上で論理式はとても重要です、そもそも論理式とは何か、どうして論理式というのか、論理式の作り方、論理式の使い方について解説します。そもそも論理式という言い方が分かりずらいと思う。なぜエクセルでは論理式というのか… Microsoftのヘルプによると、IF関数 構文:IF(logical_test,
先頭の数値、最後の数値を取り出す
数値と文字が混在した文字列から、数値だけを取り出します、先頭の数値や、最後の数値だけを取り出す方法です。A1セルに 1234abcd5678 このA1セルから、1234や5678を取り出します。先頭の数値…1234 =LOOKUP(10^17,LEFT(A1,COLUMN($1:$1))*1) COLUMN($1:…
最後の空白(や指定文字)以降の文字を取り出す
いくつかのスペースやハイフンで区切られた文字列から、最後のスペースやハイフン以降の文字列を取り出します。A1セルに、abcdefghi や abc-def-ghi これらの文字列から、ghiを取り出します。以下では、見やすいように区切り文字は"-"で説明します。
SUMIFの間違いによるパフォーマンスの低下について
再計算が終わらない… そんな経験をした人は多いと思います、原因はさまざまですが、まずは数式を見直してみましょう。単純な四則演算が遅いという事はありません、それはもうPCの問題です。時間のかかる計算としては、大量データの集計計算を多数使っている場合です。
数値範囲で表検索するVLOOKUP近似一致
・表をエクセルで扱えるように調整する ・VLOOKUP関数の近似一致 ・VLOOKUP近似一致がどのように値を探しているか ・VLOOKUP近似一致の表を作成する場合の注意点 ・VLOOKUP関数に関する参考ページ


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

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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