時刻・時間の計算に関する技:Excel関数の技

時刻・時間の計算に関する処理について説明しています

スポンサードリンク

更新:2021/06/08;作成:2007/12/16

時刻・時間のシリアル値	時刻・時間の計算	時刻・時間の丸め処理
シリアル値を数値に直す	時刻・時間の比較	数値を時刻・時間に変換する

時刻・時間のシリアル値　　 topへ

Excelでは時刻・時間をシリアル値という値で処理します。
セルの表示形式に時刻の h:mm を設定します、セルの値としては小数値が設定されます。
8:10:15(時:分:秒)のように「:」(半角のコロン)で区切って入力することで、Excelは時刻が入力されたと判断してシリアル値として扱います。
0:00:00(午前0時)から23:59:59(午後11時59分59秒)までの時刻は、小数で0～0.99999999の範囲の値で表わされます。
シリアル値は 1日=24時間が 1 となっているので、1時間=1:00は 1/24=0.0416666666666667となります。
- 1/24=0.041666666666666・・・と割り切れない値なのでExcelでは0.0416666666666667と表示されます。
  Excelの有効桁数は15桁ですので、15桁に丸められます。
  セルの表示形式を標準とするとシリアル値をみることができます。(これがセルの値です)
  なお、セルにはセルの表示幅で四捨五入された値が表示されます。

24時間以上の場合は表示形式を [h]:mm とすることにより、25:00といった表示にすることが出来ます。

ただし、25:00と入力すると、規定の状態では時刻の表示形式 h:mm が自動で適用され1:00と表示されます。
表示形式をユーザー設定で [h]:mm とすると25:00と表示されます。

と書いていたのですが・・・、
25:00と入力すると、表示形式 [h]:mm:ss が設定されるのに気づきました。(記 2020/11/13)
いつのExcelからこうなっていたのでしょうか・・・(;^_^A
ただし、計算結果が 24:00 以上になる場合は、手作業で表示形式を　[h]:mm とする必要があります。

セルの表示形式(ユーザー定義)の設定方法

[ホーム]タブの数値グループにあるダイアログボックス起動ツールをクリックします。
または、ショートカットキー [Ctrl]+[1](テンキーの 1 は使えません)でセルの表示形式ダイアログボックスを表示します。

↓
[表示形式]タブで「ユーザー定義」を選択します。
種類に　[h]:mm　と入力して、[OK]ボタンをクリックします。

時刻・時間の計算　　 topへ

セル参照で計算する

セル番地同士の計算は普通にセルの和を求めます。=A1+B1 とか =SUM(A1:B1) として求めます。
計算結果が24時を超える場合は、表示形式を　[h]:mm とします。

数式内でシリアル値を使う

シリアル値として計算するときは、加算したい時間をダブルクォーテーションで囲みます。
Excelが内部で時刻に変換して計算してくれます。

TIME関数を利用する

TIME関数を使って、5時を加算したいときは　+TIME(5,0,0) とします。

時と分が別セルに入力されているとき、時刻表示にしたいケース

=TIME(A2,B2,0)　と入力すれば、24時未満は表示可能です。
24時を超える場合は、表示形式を[h]:mm　としても　=TIME(A2,B2,0) では表示できません。
TIME関数の引数の時は24以上の場合は、24で除算してその剰余(余り)が「時」に使用されます。
それをかいくぐるには、=(A2&":"&B2)*1　として、Excelが内部で時刻に変換してくれるのを利用します。

時刻・時間の丸め処理　　 topへ

単位時間で切り下げる

FLOOR関数を利用し、30分単位で切り下げる例です。
=FLOOR(A1,"0:30") としています。
Excel2013ではFLOOR.MATH関数に変更されました。
30分単位で切り下げるには　=FLOOR.MATH(B3,"0:30")　とします。
15分単位で切り下げるには　=FLOOR.MATH(B3,"0:15")　とします。

単位時間で切り上げる

CEILING関数を利用し、30分単位で切り上げる例です。
=CEILING("A1,"0:30") としています。
Excel2013ではCEILING.MATH関数に変更されました。
30分単位で切り上げるには　=CEILING.MATH(B3,"0:30")　とします。
15分単位で切り上げるには　=CEILING.MATH(B3,"0:15")　とします。

単位時間で四捨五入する

MROUND関数を利用して、30分単位に四捨五入する例です。
=MROUND(A,"0:30") とします。
- なお、Excel2003以前でMROUND関数を使うには「分析ツール」を組み込む必要があります。
  Excel2007以降では組み込む操作は不要です。

シリアル値を数値に直す　　 topへ

時給の計算などをするとき、数値に直して計算する必要があります。
"24:00"=1　なので　="24:00"*24=1*24=24 となります。
"7:30"*24=7.5 となります。
よって、シリアル値に24を掛けると　時.分　(時が整数部分で、分が小数部分に表示されます)となります。
日当を計算する例
計算式は　=時間のシリアル値 * 24 * 時給　とします。
分単位に直すには 24*60=1440を掛けます
="7:15"*1440　とすると　435(分)と求めることができます。
(小数誤差が問題になるようなときには分単位(整数)で計算することも有ります。)

時刻・時間の比較　　 topへ

Excelでは時刻・時間の形式（「：」（コロン)で区切って）でセルに入力すると、自動的にシリアル値に変換してくれます。
ところが、数式内で使用するとき「"」で括ると文字列として扱われることがあります。
時刻・時間を比較するとき、セル同士で比較する場合はそれほど問題にはならないと思いますが数式内での比較には注意が必要です。
時刻9：00を過ぎての出勤は遅刻とする例です。
- =IF(A2>"9:00","遅刻","OK")　とすると・・・比較がうまくできていません。
  これは"9:00"を文字列として比較しているため、A列の数値(シリアル値)が小さいと判定されているためです。
- =IF(A2>"9:00"*1,"遅刻","OK")のように、"9:00"*1とするとExcelがシリアル値に変換してくれます。
  - 関数を使うと、
    =IF(A2>TIMEVALUE("9:00"),"遅刻","OK")
    =IF(A2>VALUE("9:00"),"遅刻","OK")
    のように書くこともできます。
- または、TIME関数を使って=IF(A2>TIME(9,0,0),"遅刻","OK")　とする方法も使えます。

数値を時刻・時間に変換する　　 topへ

時刻が数値で入力されているデータがあり、それをシリアル値に変換して計算したいときがあります。

出社時刻 850、退社時刻 1710 とカンマ無しで入力されていたとします。
1. 850の場合：INT(B2/100)*60+MOD(B2,100)のように分単位に直して計算することができます。
  =(INT(B2/100)*60+MOD(B2,100)-(INT(A2/100)*60+MOD(A2,100)))/1440
2. TEXT関数を利用してシリアル値に直して計算する方法も使えます。
出社時刻 8.50、退社時刻 17.10 とドット区切りで入力されていたとします。
1. 整数部分を*60とし、小数部分を*100として分単位に直して計算します。
  - =(INT(B2)*60+MOD(B2,1)*100-(INT(A2)*60+MOD(A2,1)*100))/1440
  小数誤差が気になるので、
  - =(ROUND(INT(B2)*60+MOD(B2,1)*100,0)-ROUND(INT(A2)*60+MOD(A2,1)*100,0))/1440
  としてみました。
2. 小数の桁数をTEXT関数で小数点以下2桁にします。TEXT(B2,"0.00")
  更に、「.」小数点を「:」コロンにSUBSTITUTE関数で置き換えてシリアル値として計算します。
  - =SUBSTITUTE(TEXT(B2,"0.00"),".",":")-SUBSTITUTE(TEXT(A2,"0.00"),".",":")
  小数誤差が気になるので、もうひとつTEXT関数を組み合わせると、
  - =TEXT(SUBSTITUTE(TEXT(B2,"0.00"),".",":")-SUBSTITUTE(TEXT(A2,"0.00"),".",":"),"h:mm")*1
  となります。