【Office シリーズ】 時刻・時間関連:Excel講座 勤務時間の計算

勤務時間の計算

パートの人の勤務時間計算で、定時が 9:00～17:00　となっている場合の計算方法を考えてみましょう。


手計算ではD列のように各時間帯毎に計算すればいいのですが、一つの計算式にまとめるには

引かれる方の値：終業時刻（17:00）と退社時刻（C列)の値の小さい方が基準になります。

引く方の値　　：始業時刻(9:00)と出社時刻（B列)の値の大きい方が基準となります。

つまり、E列の数式『=MIN(C3,"17:00")-MAX(B3,"9:00")』で求めることができます。

なお、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　とします。


定時の勤務時間の計算と同様ですが、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"））)

としておけばよいと思います。