この記事で書きましたように、火事になりかねない状況を放置などありましたので、【ロイヤルバス】、【エイブル】ご利用時は、ご注意下さい。

Excelで関数を使い、自動で労働時間を入力されるようにする(勤務表の作り方、その4)

Excelで関数を使い、自動で定時の出社や退社時間を入力されるようにする(勤務表の作り方、その3)では、出退社の時間を基準となる年月日を入力するだけで、定時の出退社の時間が自動で入力されるようにまでしました。

次は、それに対する労働時間も自動で入力されるようにしたいと思います。



画像
これも、これまでの勤務表の作り方と同様に、関数を使い、労働時間が自動で計算され、自動で入力されています。



画像
例えば、セルのA1に入力されている 「2016/1/1」 を 「2016/2/1」 に入力し直すと、勤務表の作り方、その3までの記事で説明した内容と同じように、土日の所が連動して消えますし、入力が必要な所は、自動で入力されます。



画像
そして、残業した場合も、残業時間を合わせての労働時間を自動で計算してくれます。
この画像を見て貰うと分かると思うのですが、例えば、20時まで残業したとした場合、セルのD4に 「20:00」 と入力すれば、右隣の労働時間もそれに合わせてその分増えます。



入力方法
労働時間が短時間で、そもそも休憩が無いと言う場合を除いては、休憩時間があり、通常は休憩時間は、労働時間から省かれますので、その基準となる休憩時間を、どこかのセルに入力しておく必要があります。


画像
例えば、先ほどの画像で説明しますと、下の方のセルに 「休憩時間」 と入力されていて、その下のセルに 「0:45」 と入力されているのが分かると思います。
これが、基準となる休憩時間で、この場合は、45分が休憩時間として、差し引かれます。
つまり、8時半に出社して17時に退社する場合は、会社にいた時間は、8時間半になりますが、45分差し引かれますので、労働時間は7時間45分で計算されます。



セルへの入力は、上のセルから順に下のように入力していきます。
セルの書式設定をしないと正しく表示しません。(設定方法は下で説明しています)

=IF(D4<>"",(D4-C4)-E40,"")
=IF(D5<>"",(D5-C5)-E40,"")
=IF(D6<>"",(D6-C6)-E40,"")
=IF(D7<>"",(D7-C7)-E40,"")
=IF(D8<>"",(D8-C8)-E40,"")
=IF(D9<>"",(D9-C9)-E40,"")
=IF(D10<>"",(D10-C10)-E40,"")
=IF(D11<>"",(D11-C11)-E40,"")
=IF(D12<>"",(D12-C12)-E40,"")
=IF(D13<>"",(D13-C13)-E40,"")
=IF(D14<>"",(D14-C14)-E40,"")
=IF(D15<>"",(D15-C15)-E40,"")
=IF(D16<>"",(D16-C16)-E40,"")
=IF(D17<>"",(D17-C17)-E40,"")
=IF(D18<>"",(D18-C18)-E40,"")
=IF(D19<>"",(D19-C19)-E40,"")
=IF(D20<>"",(D20-C20)-E40,"")
=IF(D21<>"",(D21-C21)-E40,"")
=IF(D22<>"",(D22-C22)-E40,"")
=IF(D23<>"",(D23-C23)-E40,"")
=IF(D24<>"",(D24-C24)-E40,"")
=IF(D25<>"",(D25-C25)-E40,"")
=IF(D26<>"",(D26-C26)-E40,"")
=IF(D27<>"",(D27-C27)-E40,"")
=IF(D28<>"",(D28-C28)-E40,"")
=IF(D29<>"",(D29-C29)-E40,"")
=IF(D30<>"",(D30-C30)-E40,"")
=IF(D31<>"",(D31-C31)-E40,"")
=IF(D32<>"",(D32-C32)-E40,"")
=IF(D33<>"",(D33-C33)-E40,"")
=IF(D34<>"",(D34-C34)-E40,"")

E40が先ほど書いた、基準となる休憩時間を入力したセルになります。
また、 「=IF(D4<>"",(D4-C4)-E40,"")」 を例に説明しますと、(退社時間-出社時間)-休憩時間を(D4-C4)-E40の所でしています。
ただ、早退したりした場合なども休憩時間が必ず引かれますので、そう言った場合は、労働時間の手入力が必要です。



残業する場合に休憩時間が増える場合
残業する場合は、定時になった時点で一旦休憩があるなど、休憩時間が増えるという場合があると思います。
上で書いた説明では、基準となる休憩時間を入力したセルが必要と言う事を書きましたが、この残業で休憩時間が増える場合は、これとは別に残業時の休憩時間を、どこかのセルに入力しておく必要があります。



画像
例えば、この画像で説明しますと、 「残業時の休憩時間」 と入力してあるセルの下に 「0:15」 と入力してあるのが分かると思います。
残業した時は、通常の休憩時間として入力してある時間と、残業した時の休憩時間として入力してある時間の両方が会社に居た時間から差し引かれます。

残業時に休憩時間が増える場合は、下のように入力します。
ただ、早退をした場合なども、通常の休憩時間+残業時の休憩時間が差し引かれますので、そう言った場合は、労働時間の手入力が必要です。

=IF(D4="17:00",(D4-C4)-E40,IF(D4="","",(D4-C4)-E40-E43))
=IF(D5="17:00",(D5-C5)-E40,IF(D5="","",(D5-C5)-E40-E43))
=IF(D6="17:00",(D6-C6)-E40,IF(D6="","",(D6-C6)-E40-E43))
=IF(D7="17:00",(D7-C7)-E40,IF(D7="","",(D7-C7)-E40-E43))
=IF(D8="17:00",(D8-C8)-E40,IF(D8="","",(D8-C8)-E40-E43))
=IF(D9="17:00",(D9-C9)-E40,IF(D9="","",(D9-C9)-E40-E43))
=IF(D10="17:00",(D10-C10)-E40,IF(D10="","",(D10-C10)-E40-E43))
=IF(D11="17:00",(D11-C11)-E40,IF(D11="","",(D11-C11)-E40-E43))
=IF(D12="17:00",(D12-C12)-E40,IF(D12="","",(D12-C12)-E40-E43))
=IF(D13="17:00",(D13-C13)-E40,IF(D13="","",(D13-C13)-E40-E43))
=IF(D14="17:00",(D14-C14)-E40,IF(D14="","",(D14-C14)-E40-E43))
=IF(D15="17:00",(D15-C15)-E40,IF(D15="","",(D15-C15)-E40-E43))
=IF(D16="17:00",(D16-C16)-E40,IF(D16="","",(D16-C16)-E40-E43))
=IF(D17="17:00",(D17-C17)-E40,IF(D17="","",(D17-C17)-E40-E43))
=IF(D18="17:00",(D18-C18)-E40,IF(D18="","",(D18-C18)-E40-E43))
=IF(D19="17:00",(D19-C19)-E40,IF(D19="","",(D19-C19)-E40-E43))
=IF(D20="17:00",(D20-C20)-E40,IF(D20="","",(D20-C20)-E40-E43))
=IF(D21="17:00",(D21-C21)-E40,IF(D21="","",(D21-C21)-E40-E43))
=IF(D22="17:00",(D22-C22)-E40,IF(D22="","",(D22-C22)-E40-E43))
=IF(D23="17:00",(D23-C23)-E40,IF(D23="","",(D23-C23)-E40-E43))
=IF(D24="17:00",(D24-C24)-E40,IF(D24="","",(D24-C24)-E40-E43))
=IF(D25="17:00",(D25-C25)-E40,IF(D25="","",(D25-C25)-E40-E43))
=IF(D26="17:00",(D26-C26)-E40,IF(D26="","",(D26-C26)-E40-E43))
=IF(D27="17:00",(D27-C27)-E40,IF(D27="","",(D27-C27)-E40-E43))
=IF(D28="17:00",(D28-C28)-E40,IF(D28="","",(D28-C28)-E40-E43))
=IF(D29="17:00",(D29-C29)-E40,IF(D29="","",(D29-C29)-E40-E43))
=IF(D30="17:00",(D30-C30)-E40,IF(D30="","",(D30-C30)-E40-E43))
=IF(D31="17:00",(D31-C31)-E40,IF(D31="","",(D31-C31)-E40-E43))
=IF(D32="17:00",(D32-C32)-E40,IF(D32="","",(D32-C32)-E40-E43))
=IF(D33="17:00",(D33-C33)-E40,IF(D33="","",(D33-C33)-E40-E43))
=IF(D34="17:00",(D34-C34)-E40,IF(D34="","",(D34-C34)-E40-E43))

これは、退社時間が17:00であれば、退社時間-出社時間-休憩時間をして、退社時間が17:00でなければ、退社時間-出社時間-休憩時間-残業時の休憩時間をするという式になります。
また、退社時間を入力するセルが空白の場合は、労働時間も空白になります。

上の式は、定時での退社時間を基準としているので、定時での退社時間が違う場合は、 「17:00」 の所を定時の時間に変更します。

「=IF(D4="17:00",(D4-C4)-E40,IF(D4="","",(D4-C4)-E40-E43))」 を例として、仮に18:00が定時だった場合は、 「=IF(D4="18:00",(D4-C4)-E40,IF(D4="","",(D4-C4)-E40-E43))」 となります。



セルの書式設定

画像
上の数式だけ入力しても、セルの書式設定をしないと、この画像のように正しく表示されないので、セルの書式設定が必要です。



画像
1日~31日までの労働時間のセルを選び、選んだセルの上で 「右クリック」  「セルの書式設定」 をクリックします。



画像
分類の中から 「時刻」 をクリックし、 種類の中から 「13:30」 をクリックし、 「OK」 をクリックします。
すると、正しい表示になったと思います。



関連記事
Excelで関数を使い、自動で日付を入力されるようにする(勤務表の作り方、その1)

Excelで関数を使い、自動で曜日を入力されるようにする(勤務表の作り方、その2)

Excelで関数を使い、自動で定時の出社や退社時間を入力されるようにする(勤務表の作り方、その3)

Excelで関数を使い、自動で労働時間を合計されるようにする(勤務表の作り方、その5)

Excelで関数を使い、自動で残業時間が計算されるようにする(勤務表の作り方、その6)

Excelで関数を使い、自動で祝日が入力されるようにする(勤務表の作り方、その7)

Excelで関数を使い、祝日の出退社時間が自動で空白になるようにする(勤務表の作り方、その8)

スマホなどのモバイルなどでも見られている方へ

このブログは、Windows10で23インチ画面のデスクトップパソコン(自作)を使用し、作成・確認をしています。
また、今の所、パソコンでこのブログを見られている方の方が多いようなので、パソコンで見る事を想定してブログの作成をしています。

その為、スマホなどを使って見ると見辛いかな?と思う表示になってしまう記事が一部あります。
見辛くなってしまい、すみません。<(_ _)>

この記事へのコメント