1. Home » 
2. エクセル関数一覧表 » 
3. Excel関数の目次 » 
4. 時間・時刻の関数

時間・時刻の関数の使い方:Excel関数

• Excelでの時間・時刻のシリアル値から時,分,秒を取出す関数(HOUR関数,MINUTE関数,SECOND関数)
  時,分,秒の数値から時間・時刻のシリアル値に直す関数(TIME関数)
  文字列からシリアル値に直す関数(TIMEVALUE関数)
  について解説しています。
• 時給計算の仕方を例に挙げて説明しています。

2023/2/1

シリアル値：Excelでは日付や時刻はシリアル値で処理されます。

• Windows版のExcelでは 日付は1900年1月1日を「1」、1900年1月2日を「2」・・・と、数値で表します。
  時間・時刻は「1日(24時間)」を「1」として、12:00は「0.5」、6:00は「0.25」と小数点以下の数値で表します。
  この数値のことをシリアル値と呼びます。
  日付が入力されているセルの表示形式を「標準」とすると、日付データが数値で表示されます。この数値がシリアル値と呼ばれるものです。
• なお、Windows版は1900日付システム、Macintosh版は190４日付システムとシリアル値の基準が異なっていました。
  Excel 2016 for Mac および Excel for Mac 2011 では、1900日付システムが使用されました。
  このバージョン以降では、Windows 版 Excel との日付の互換性が保証されています。
  • Macintosh版では「1904年1月2日」が「１」となっています。 「1904年1月1日」が「0」から始まっています。1904日付システムと呼ばれます。
    Excel版では「1900年1月1日」が「1」となっています。1900日付システムと呼ばれています。
    よって、Windows版とMacintosh版でExcelファイルをやり取りすると、日付がずれる場合があります。
  • Excel2007以降ではExcelのオプションで設定します。[ファイル]タブをクリックして、[オプション]を選択します。
    [詳細設定]を選択して、「次のブックで計算するとき」で「1904年から計算する」にチェックを入れます。
    
  • Excel2003以前では【ツール】→【オプション】で【計算方法】タブの『1904年から計算する』にチェックを入れて、シリアル値の起点を変更します。

時刻データから「時」「分」「秒」を取り出す(シリアル値から数値への変換)　　topへ
　アワー
=HOUR(時刻のシリアル値)　　　：時間を取り出します。0〜23(時)の範囲の整数になります。
　ミニット
=MINUTE(時刻のシリアル値)　：分を取り出します。　0〜59(分)の範囲の整数になります。
　セカンド
=SECOND(時刻のシリアル値)　：秒を取り出します。　0〜59(秒)の範囲の整数になります。
　　　(注)　時間にはシリアル値を使用します。

• 【問題】 出社時間、退社時間、時給からその日の日給を計算しましょう。
  セルD5,D7,D8,D9,D10,D11に数式を入れましょう。
  

  	B	C	D
  2	時給計算
  3	時給	1,000
  4	出社時刻	退社時刻	勤務時間
  5	8:00:10	17:20:20	9:20:10
  6
  7	勤務時間	時間	9
  8		分	20
  9		秒	10
  10	日給計算例	シリアル値	9,336.11
  11		時間単位	9,333.33

• 【解答例】
  

  	B	C	D
  2	時給計算
  3	時給	1,000
  4	出社時刻	退社時刻	勤務時間
  5	8:00:10	17:20:20	=C5-B5
  6
  7	勤務時間	時間	=HOUR(D5)
  8		分	=MINUTE(D5)
  9		秒	=SECOND(D5)
  10	日給計算例	シリアル値	=D5*24*C3
  11		時間単位	=D7*C3+D8/60*C3

• D5セルの　勤務時間=退社時間-出社時間　=C5-B5　で求められます。
  D7セル　勤務時間から時間を取り出すには　=HOUR(D5)　とします。
  D8セル　勤務時間から分を取り出すには　=MINUTE(D5)　とします。
  D9セル　勤務時間から分を取り出すには　=SECOND(D5)　とします。

• シリアル値で計算する

  • 勤務時間（D5)×時給(C3)　では求めることができません。
    時間データにシリアル値が使われているためです。
    この問題の例では勤務した時間は　=C5-B5="9:20:10"　です。
    この　"9:20:10"はシリアル値で数値に直すと、0.38900463　となります。
    シリアル値 "9:20:10"*24=9.336111111　となります。これが勤務時間 9.3時間となります。
    シリアル値*24　で何時間か？の数値を求めることができます。
    これに時間給を掛けるとその日の勤務時間に対する日給を求めることができます。
  • 勤務時間*24*時給　つまり、=D5*24*C3　とします。

• 時間単位で計算する

  • 時間と分を取り出してから計算することもできます。
    この場合はHOUR、MINUTE関数を使って時間、分を取り出して計算します。　
  • 時間×時給＋(分÷60)×時給　=D7*C3+D8/60*C3　とします。(秒は無視しています)
  • =HOUR(D5)*C3+MINUTE(D5)/60*C3　と1セルでまとめることもできます。

時給の四捨五入

• 上記の結果から分かると思いますが、秒単位まで表示していた場合シリアル値での計算では秒単位の日給が計算され、実情と異なってしまいます。(通常は分単位までと思います)
• また、日給計算結果には小数点以下の金額が計算されていますので、日々の日給は整数の単位に四捨五入、切り上げなどして丸めておく必要があります。

• 四捨五入の例です（9333となります）　　　　　　　　　
  • =ROUND(D5*24*C3,0)では秒単位まで計算され「9336」となるので、時と分だけ取出して計算します。
  • =ROUND(HOUR(D5)*C3+MINUTE(D5)/60*C3,0）
• 30分単位で勤務時間を切り上げて計算する場合(9500となります)
  • "9:20:10"　を30分単位で切り上げて　"9:30"　として計算します。
    =CEILING(D5,"0:30")*24*C3
• 30分単位で勤務時間を切り捨てて計算する場合（9000となります）　　　　　　　　　
  • "9:20:10"　を30分単位で切り下げて　"9:00"　として計算します。
    =FLOOR(D5,"0:30")*24*C3

（おまけ）時刻を数値に変換する

• 時刻「10:30」を数値の「10.5」や「時.分」に変換する例です。
  

(関連事項)

• ROUND 、FLOOR関数はこちらへ
• 勤務時間の計算について

時刻に対応する小数(シリアル値)を返します　　　topへ
　タイム
=TIME(時,分,秒)
　　時：0 〜 32767 の範囲で指定します。23 を超える値は 24 で除算され、剰余が時間として計算されます。
　　分：0 〜 32767 の範囲で指定します。59 を超える値は時と分に変換されます。
　　秒：0 〜 32767 の範囲で指定します。59 を超える値は時、分、秒に変換されます。

• 【問題】 時刻の時、分、秒が別々のセルに表示してあります。
  E3セルには『時：分：秒』　E4セルには『時：分』と表示しなさい。
  

  	B	C	D	E
  2	時	分	秒	時：分：秒
  3	11	22	3	11:22:3
  4	13	10		13:10

• 【解答例】
  

  	B	C	D	E
  2	時	分	秒	時：分：秒
  3	11	22	3	=TIME(B3,C3,D3)
  4	13	10		=TIME(B4,C4,0)

1. E3:E4セルは次のように表示されました。
   
2. Excel2007以降は[ホーム]タブの[表示形式]→[その他の表示形式]を実行します。
   ショートカットキーは[Ctrl]+[1](テンキーの1は使用不可)です。
   Excel2003以前は【書式】→【セル】で表示形式を変更します。
   
3. E3セルは秒単位まで表示するので『13:30:55』 、E4セルは分単位まで表示するので『13:30』と、それぞれ設定します。
   
4. セルの表示が変わりました。
   

時間の合計　　表示形式に注意が必要です。　　　　topへ
　　　標準では、24時表示(時刻表示)になっているため、24時を超える値は正しく表示されません。
　　　下図のように、合計のセルの表示形式を[h]:mmとします。

1. B7セルには期待する(正しい)合計値が表示されていません。
   B9セルを選択します。
   
2. [ホーム]タブの数値グループにあるダイアログボックスをクリックします。
   ショートカットキーは[Ctrl]+[1]です。
   
   • Excel2007以降は[ホーム]タブの[表示形式]→[その他の表示形式]を実行してもOKです。
     
   • Excel2003以前は、メニューバーの【書式】→【セル】を選択して、セルの書式設定を表示します。
3. 【表示形式】タブで「ユーザー定義」を選択し、種類に　[h]:mm　と入力します。
   
4. B7セルの表示形式は　h:mm　、B9セルの表示形式は　[h]:mm　とした例です。
   B7セルには24時を超えた分しか表示されず、期待した表示になりません。
   B9セルは表示形式を　[h]:mm　として、期待した表示になっています。
   

文字列で表された時刻を小数(シリアル値)に変換します　　topへ
　タイムバリュー
=TIMEVALUE(時刻文字列)
　　時刻のシリアル値：0〜0.99999999 の値で、0:00:00(午前0時)〜23:59:59 (午後11時59分59秒) までの時刻を返します。

• 文字データを時刻データ(0〜1の小数、シリアル値）に変換します。
  TIMEVALUE関数は24時までのシリアル値になります。
  24:00以上を扱う時はVALUE関数を使うことができます。
  文字列の日付を扱う場合はDATEVALUE関数を使います。

• 【問題】 下表をコピーするとB2:B5セルには時刻のデータが入力されます。
  C2〜C5には文字列の時刻データを作成するために数式=TEXT(B2,"[h]:mm")が入力されています。
  C列の文字列の時刻をシリアル値に変換して、D列、E列に表示しなさい。
  • B列の24:00以上を表示するにはセルの表示形式をユーザー定義で「[h]:mm」とします。
    文字列として時刻を表示するには「'6:00」のように「'」（アポストロフィ）を頭に付けます。
    または、=TEXT（"6:00","h:mm")　のようにTEXT関数で変換します。

  	B	C	D	E
  2	6:00	=TEXT(B2,"[h]:mm")	0.25	0.25
  3	12:00	=TEXT(B3,"[h]:mm")	0.5	0.5
  4	24:00	=TEXT(B4,"[h]:mm")	0	1
  5	36:00	=TEXT(B5,"[h]:mm")	0.5	1.5

• 【解答例】
  D4セル以降のようにTIMEVALU関数では24:00以上はうまく計算されません。
  D4セルは　TIMEVALUE("24:00")=0　となります。
  24:00以上を計算する時には、E列のようにVALUE関数を使用します。
  
  D:E列の表示形式を　[h]:mm とすると下図のようになります。
  
• 実は関数を使わず、C2*1　と文字列のセルと演算をすると、Excelが勝手に数値(シリアル値)に変換してくれます。
  下図ではD列の表示形式を　[h]:mm としています。
  TIMEVALUE関数やVALUE関数は必要ないとも言えます。
  

【ステップアップ】時間を単位時間で切り捨てる

• 30分単位で切り捨てて計算する場合には　=FLOOR(値,"0:30")　とします。
• しかし、時間は24時間を1としたシリアル値で換算されていますので、小数部で誤差が出ることがあります。
  FLOOR関数でうまくいかない場合もありますので、そういった場合には、
  (キーボードからの入力ではほとんど問題ないですが、オートフィルなどで連続データを入力した時などにこの問題を生じます。）
  • =FLOOR((TIMEVALUE(TEXT(D5,"hh:mm"))),"0:30")*24*D3
  のようにTIMEVALUE関数を使う方法があります。
• FLOOR関数やCEILING関数の使い方
  CEILING関数(倍数に切り上げる),FLOOR関数(倍数に切り下げる)の使い方

スポンサードリンク

PageViewCounter

Since2006/2/27