勤務時間計算の例：Excel 実用編

作成:2021/1/19

はじめに　勤務時間計算の条件

• 一つのセルでいろんな条件を付けて計算しようとすると、長い数式になり修正が非常に大変になります。
• このページでは、なるべく平易な分かりやすい数式となるように、各時間帯別に勤務時間を計算する方法で説明しています。

勤務時間計算の条件設定

1. 通常勤務の時間帯　：　 8:00〜17:00（休憩時間　12:00〜13:00）
2. 残業時間帯　　　　：　17:00〜22:00
3. 深夜残業時間帯　　：　22:00〜29:00 (翌朝 5:00）
4. 勤務時間の開始時刻は8:00として計算します。
5. 勤務時間の終了時間は退勤時刻までを1分単位で計算します。

計算表のスタイルを決める　　　　　　Topへ

• 計算表のスタイル
  • 24時以降は25:00，29:00 といった風に入力しています。
    セルの表示形式を [h]:mm とします。

• 計算表のスタイル設定例
  

  	B	C	D	E	F	G	H	I	J
  2	社員コード				氏名
  3	計算年月
  4	日	曜日	出勤時刻	退勤時刻	通常勤務(1)	休憩時間	通常勤務(2)	残業勤務	深夜勤務
  5
  6					8:00	12:00	13:00	17:00	22:00
  7					12:00	13:00	17:00	22:00	29:00

勤務時間の計算式　　　　　　　Topへ

• 通常勤務時間帯(1)　8:00〜12:00の勤務時間を求める数式を考えます。
  勤務時間=退勤時間-出勤時間　で求めることができます。
  この時間帯での勤務時間を求めるには、H8セルは=退勤時間-出勤時間　を求めればよいので
  • 数式内に計算開始時刻(8:00)と計算終了時刻(12:00)を書くと
    =MIN(退勤時刻,"12:00")-MAX(出勤時刻,"8:00")
    となります。
  • 出勤時刻はD8セルに入力されており、計算終了時刻の12:00はF7セルに入力されているので、セル参照すると、
    =MIN(E8,F7)-MAX(D8,F6)
    となります。
  • 小数誤差の対策として一旦TEXT関数で丸め処理し、*1することでシリアル値に戻します。
    =TEXT(MIN(E8,F7)-MAX(D8,F6),"h:mm")*1
  • この数式を右方向へコピーしたいので、セル参照を絶対参照(複合参照)に変更すると、
    F8セル：=TEXT(MIN($E8,F$7)-MAX($D8,F$6),"h:mm")*1　となります。
    ただし、この数式を右方向へコピーすると #VALUE! エラーとなります。
    
• 次にエラー処理が必要になります。
  退勤時刻が各時間帯の開始時刻より早く、出勤時刻が各時間帯の終了時刻より遅い場合には、
  その勤務時間帯には出社していないことになります。
  計算結果が負となるので、最大値を0とすればよいので
  該当部分は　MAX(0,MIN($E8,F$7)-MAX($D8,F$6)　となり、
  =TEXT(MAX(0,MIN($E8,F$7)-MAX($D8,F$6)),"h:mm")*1
  となります。
  ところが、9行目のように下方向へ数式をコピーすると意味のない時間が計算されてしまいます。
  
• 更に、出勤時刻、退勤時刻が未入力だと意味のない時間が表示されてしまいます。
  空白の処理を加えておく必要があります。
  F8セル：=IF(COUNT($D8:$E8)<2,"",TEXT(MAX(0,MIN($E8,F$7)-MAX($D8,F$6)),"h:mm")*1)
  G8〜J8にフィルハンドルをドラッグして、コピーすると数式は完成です。
  更に、F8:J8を選択して、フィルハンドルを下方向へドラッグして数式をコピーすれば計算表は完成です。
  9行目のF9セルは D9:E9セルが空欄なので、計算結果が空欄になっています。
  

• (完成例)
  

  	B	C	D	E	F	G	H	I	J
  2	社員コード				氏名
  3	計算年月
  4	日	曜日	出勤時刻	退勤時刻	通常勤務(1)	休憩時間	通常勤務(2)	残業勤務	深夜勤務
  5
  6					8:00	12:00	13:00	17:00	22:00
  7					12:00	13:00	17:00	22:00	29:00
  8			7:50	11:30	3:30	0:00	0:00	0:00	0:00
  9			7:50	12:40	4:00	0:40	0:00	0:00	0:00
  10			7:50	14:40	4:00	1:00	1:40	0:00	0:00
  11			7:50	18:40	4:00	1:00	4:00	1:40	0:00
  12			7:50	23:40	4:00	1:00	4:00	5:00	1:40

分単位(整数値)での計算例(演算誤差の防止)　　　　　Topへ

• 整数値(分単位)で計算することによって、演算誤差を防ぐことができます。
  計算結果は分単位となっています。
• F6〜J7の基準時刻は分計算のため整数値に直しています。
  基本の数式は=ROUND(時刻*1440,0)　としています。
  (1:00＝1/(24*60)なので、24*60=1440としています)
• (完成例)
  

  	B	C	D	E	F	G	H	I	J
  2	社員コード				氏名
  3	計算年月
  4	日	曜日	出勤時刻	退勤時刻	通常勤務(1)	休憩時間	通常勤務(2)	残業勤務	深夜勤務
  5
  6					480	720	780	1020	1320
  7					720	780	1020	1320	1740
  8			7:50	11:30	210	0	0	0	0
  9			7:50	12:40	240	40	0	0	0
  10			7:50	14:40	240	60	100	0	0
  11			7:50	18:40	240	60	240	100	0
  12			7:50	23:40	240	60	240	300	100

  • F6:=ROUND("8:00:00"*1440,0)
  • G6:=ROUND("12:00:00"*1440,0)
  • H6:=ROUND("13:00:00"*1440,0)
  • I6:=ROUND("17:00:00"*1440,0)
  • J6:=ROUND("22:00:00"*1440,0)
  • F7:=ROUND("12:00:00"*1440,0)
  • G7:=ROUND("13:00:00"*1440,0)
  • H7:=ROUND("17:00:00"*1440,0)
  • I7:=ROUND("22:00:00"*1440,0)
  • J7:=ROUND("29:00"*1440,0)
  • F8:=IF(COUNT($D8:$E8)<2,"",MAX(0,MIN(ROUND($E8*1440,0),F$7)-MAX(ROUND($D8*1440,0),F$6)))
    数式内で、出勤時刻と退勤時刻を分単位に直しています。

スポンサードリンク

PageViewCounter
　
　Since2006/2/27