1. Home » 
2. Excel講座の総目次 » 
3. Excel(エクセル)講座 Q&A(Tips) » 
4. 時刻・時間関連

時刻・時間関連:Excel講座 Q&A(Tips)

時刻の計算　　　　　　　Topへ

• 勤務時間の計算を例として説明します。

1. 出社時刻 8:00　退社時刻 18:30　の場合
   • ="18:30"-"8:00"　と数式を入力します。(時刻は『"』(ダブルクォーテーションマーク)でくくります。)　
   • 0.4375 と表示されます。 0.4375 はシリアル値と呼ばれる値ですので、これを　10:30　と表示するには
     【書式】→【セル】を選択し、【表示形式】タブの分類で【時刻】、種類で【13:30】を選択すると、「10:30」と表示されます。
2. 出社時刻がB3セル、退社時刻がC3セルに入力されている場合、D3セルに勤務時間を求めるには
   「=C3-B3」　となります。　セルの表示形式は(1)と同様に時刻に変更します。
   

   	B	C	D
   2	出社時刻	退社時刻	勤務時間
   3	8:00	18:30	=C3-B3

3. 退社時刻が 24:00　をすぎる場合
   出社時刻が 18:00　で、退社時刻が　2:00 の時は
   ="2:00"-"18:00"　とすると　-0.6667　といった値が表示され、表示形式を時刻にすると　#####　となります。マイナスになる時刻の計算はできないということです。
   1. =1+"2:00"-"18:00"　と、　「1」を加えると　8:00　と計算できます。
      この場合、IF関数と組み合わせて、退社時刻が出社時刻より小さい時は「1」を加えて計算するようにします。
      =IF(C4<B4,1+C4-B4,C4-B4)
      • =(C4-B4<0)+C4-B4　と書くこともできます。TRUE=1,FALSE=0となるのを利用しています。
      • =MOD(C4-B4,1)　と書くこともできます。時刻は小数値になるので計算結果の正の小数値を取り出しています。
   2. ="26:00"-"18:00"　と、　2:00　を　24:00+2:00=26:00　としても計算できます。
      このとき　26:00　と入力して、表示形式が　時刻の『13:30』　としてあると、2:00　と表示されます。
      26:00　と表示するには、表示形式を『ユーザー定義』で種類に『[h]：mm』　と入力します。
      

      	B	C	D
      2	出社時刻	退社時刻	勤務時間
      3	18:00	2:00	=1+C3-B3
      4	18:00	2:00	=IF(C4-B4<0,1+C4-B4,C4-B4)
      5	18:00	26:00	=C5-B5

【関係のない余談：どうしてもマイナスの計算をしたい】

• ただし、使う前に　Microsoft　サポート技術情報　↓を参照願います。
  Excel の 1900 年を基準とした日付方式と 1904 年を基準とした日付方式の違いについて
  https://support.microsoft.com/ja-jp/help/214330/differences-between-the-1900-and-the-1904-date-system-in-excel

1. [ファイル]タブをクリックして、バックステージビューを表示します。
   
2. [オプション]をクリックします。
   
3. [詳細設定]を選択して、次のブックで計算するときの「1904年から計算する」にチェックを入れます。
   
4. 下図のように　=1:00 - 15:00 = -14:00　と負になる時刻の計算ができます。
   

【おまけ：その他の数式の書き方】

="3:00"+"2:00"
=TIMEVALUE("3:00")+TIMEVALUE("2:00")
="3時00分"+"2時00分"
=TIMEVALUE(3時00分)+TIMEVALUE(2時00分)
=3/24+2/24
などと書くこともできます。

【おまけ：マイナスの時間を表示する】

文字列で表示する例です。
=IF(A1-B1<0,TEXT(ABS(A1-B1),"-"&"h:mm"),TEXT(A1-B1,"h:mm"))
負になる場合は絶対値に「-」を付け加えて文字列にしています。

時刻のシリアル値　　　　　　Topへ

• 時刻のシリアル値は、0〜0.99999999の範囲にある値で、0:00:00(午前0時)から 23:59:59 (午後11時59分59秒)までの時刻を表します。

1. セルに「24:00」と入力すると、数式バーには「1900/1/1 0:00:00」と表示されます。
   "24:00"= 1　ですので　"1:00"= 1/24　となります。
2. では、"18:30"-"8:00"="10:30" を 10.5 と表示したい時はどうすればよいでしょうか?
   ="18:30"-"8:00"=0.4375　とシリアル値が表示されます。(セルの表示形式は標準とします)
   =("18:30"-"8:00")*24　=　10.5　とします。
3. 逆に、10.5を、10:30　としたい時は
   =10.5/24　=　0.4375　として、セルの表示形式を「時刻」の「13：30」とします。

勤務時間の計算　　　　　　Topへ

パートの人の勤務時間計算で、定時が 9:00〜17:00　となっている場合の計算方法を考えてみましょう。
　　　　　9:00　　　　　　　　17:00
　(A)　<---+-----------------+----->
　(B)　　　　　　<-----------+----->　　
　(C)　　　　　　<------->

• 手計算ではD列のように各時間帯毎に計算すればいいのですが、一つの計算式にまとめるには
  • 引かれる方の値：終業時刻（17:00）と退社時刻（C列)の値の小さい方が基準になります。
  • 引く方の値　　：始業時刻(9:00)と出社時刻（B列)の値の大きい方が基準となります。
  • つまり、E列の数式『=MIN(C3,"17:00")-MAX(B3,"9:00")』で求めることができます。
    

    	B	C	D	E	F
    2	出社時刻	退社時刻	勤務時間	勤務時間	答え
    3	8：00	17：10	="17:00"-"9:00"	=MIN(C3,"17:00")-MAX(B3,"9:00")	8:00
    4	10：00	17：20	="17:00"-B5	=MIN(C4,"17:00")-MAX(B4,"9:00")	7:00
    5	10：00	15：20	=C5-B5	=MIN(C5,"17:00")-MAX(B5,"9:00")	5:20

• なお、F5セルのような30分未満は切り捨てて計算するには、FLOOR関数と組み合わせます。
  =FLOOR(MIN(C5,"17:00")-MAX(B5,"9:00"),"0:30")　　答え=5:00
• 逆に切り上げたい場合にはCEILING関数を使います。
  =CEILING(MIN(C5,"17:00")-MAX(B5,"9:00"),"0:30")　　答え=5:30

• 次に残業時間の計算を加えてみましょう。
  • 定時は 9:00〜17:00
    残業は　17:00〜22:00　とします。
    

    	B	C	D	E	F	G
    2	出社時刻	退社時刻	定時の時間	残業の時間	定時	残業
    3	8：00	19：10	=MIN(C3,"17:00")-MAX(B3,"9:00")	=MIN(C3,"22:00")-MAX(B3,"17:00")	8：00	2：10
    4	10：00	20：20	=MIN(C4,"17:00")-MAX(B4,"9:00")	=MIN(C4,"22:00")-MAX(B4,"17:00")	7：00	3：20
    5	10：00	15：20	=MIN(C5,"17:00")-MAX(B5,"9:00")	=MIN(C5,"22:00")-MAX(B5,"17:00")	5：20	0

  • 定時の勤務時間の計算と同様ですが、E5セルはエラーになってしまいます。
    IF関数を使って、残業開始時刻前に退社した場合は「空白」とするようにします。
    =IF(C5-"17:00"<0,"",MIN(C5,"22:00")-MAX(B5,"17:00"））
  • エラーとなるのは計算結果が負の値になるときなので、最小値を「0」とするようにします。
    =MAX(0,MIN(C5,"22:00")-MAX(B5,"17:00"））
• また定時の計算でも、B列やC列が入力されていない時は「空欄」としておく必要があります。
  出社時刻が残業開始時刻（17:00）より遅い場合も定時の時間は「0」となります。
  
  • =IF(OR(B3="",C3="",B3-"17:00">0),"",MIN(C3,"17:00")-MAX(B3,"9:00"））
    =IF(OR(COUNT(B3:C3)<2,B3-"17:00">0),"",MIN(C3,"17:00")-MAX(B3,"9:00"））
    =IF(OR(B3="",C3=""),"",MAX(0,MIN(C3,"17:00")-MAX(B3,"9:00"））)
    =IF(COUNT(B3:C3)<2,"",MAX(0,MIN(C3,"17:00")-MAX(B3,"9:00"））)
    としておけばよいと思います。

【計算誤差(小数誤差)の防止】

時刻のシリアル値は1未満の小数値になっています。上記のような計算によって誤差を生じることがあります。
計算誤差を防ぐ方法の一つとして、TEXT関数を使う方法を書いてみます。
=MIN(C3,"22:00")-MAX(B3,"17:00"）
を
=TEXT(MIN(C3,"22:00")-MAX(B3,"17:00"),"h:mm")
とします。これは文字列「2：10」となり計算に使えないので、
=VALUE(TEXT(MIN(C3,"22:00")-MAX(B3,"17:00"）,"h:mm"))
　として、シリアル値に戻します。

• =TEXT(MIN(C3,"22:00")-MAX(B3,"17:00"),"h:mm")*1　
• =--TEXT(MIN(C3,"22:00")-MAX(B3,"17:00"),"h:mm")
• と書くこともできます。

【30分単位で丸める】

時間の計算で「30分単位で丸める」ことが必要な時は
=FLOOR(VALUE(TEXT(MIN(C3,"22:00")-MAX(B3,"17:00"）,"h:mm")),"0:30")
とします。
注意：毎日の残業時間の端数を切り捨てることは、労働基準法違反となり認められないようです。
あくまでも計算の例としてご覧ください。
　　　以上をまとめると、下記のように長〜い式になります。

	B	C	D	E	F	G
2	出社時間	退社時間	定時の時間	残業の時間	定時	残業
3	7：50	19：10	=IF(OR(COUNT(B3:C3)<2,B3-"17:00">0),"", FLOOR(VALUE(TEXT(MIN(C3,"17:00")- MAX(B3,"9:00"),"h:mm")),"0:30"))	=IF(OR(COUNT(B3:C3)<2,C3-"17:00"<0),"", FLOOR(VALUE(TEXT(MIN(C3,"22:00")- MAX(B3,"17:00"),"h:mm")),"0:30"))	8：00	2：00

• D3セルは
  =IF(COUNT(B3:C3)=2,FLOOR(VALUE(TEXT(MAX(0,MIN(C3,"17:00")-MAX(B3,"9:00")),"h:mm")),"0:30"),"")
  と書くこともできます。
• E3セルは
  =IF(COUNT(B3:C3)=2,FLOOR(VALUE(TEXT(MAX(0,MIN(C3,"22:00")-MAX(B3,"17:00")),"h:mm")),"0:30"),"")
  と書くこともできます。

テンキーで時刻を簡単に入力する　「:」を「..」で入力する(オートコレクト)

• 時刻の入力で「:」(コロン)を入力するのが面倒くさい・・・といわれる方がおられます。
• テンキーの「..」(ドットを2個）と入力して、「:」に変換する方法にオートコレクトを使用することができます。
• オートコレクトはExcelだけではなくWordにも適用されます

1. [ファイル]タブをクリックして、バックステージビューを表示します。
   
2. [オプション]をクリックします。
   
3. [文書校正]を選択して、[オートコレクトのオプション]ボタンをクリックします。
   
4. 【オートコレクト】ダイアログの【オートコレクト】タブを選択します。
   「入力中に自動修正する」にチェックを入れます。
   『修正文字列』に「..」(ドットを2個)　『修正後の文字列』に「:」(コロン)を入力します。
   【追加】ボタンをクリックします。
   

スポンサードリンク

　PageViewCounter
　
　Since2006/2/27