こんにちは
先日、Excelを受講中の生徒様より、
「月毎の勤務時間と給与が確認できるような勤怠管理表を作ってみたい」
とご相談いただきました。
Excelで勤怠管理されている方は多く、勤務形態によって色々な計算の仕方がありますが、今回は基本的な勤怠管理表の作成方法と注意事項についてご説明しますね。
時給制で、所定労働開始時間を９時とし、休憩時間60分、８時間以上の労働（９時開始のため18時以降）は時給25％増、22以降の深夜残業は時給50％増という条件で作るとします。
まずはシートの列見出しを入力します。
日付・曜日・出勤時間・退勤時間・休憩時間・通常勤務時間・時間外（8ｈ以上25％）・深夜残業（22時以降50％）・実働時間合計など。
８ｈ以上の時間外や深夜残業は時給額が変わるため、セルを分けておきます。
セルの書式設定で日付列は日付の表示形式、時間を入力するセルには「 h:mm 」を設定します。
注意したいのは、時間の合計を求めるためのセルには必ず、表示形式「 [h]:mm 」を設定するというところです。
ｈが角括弧で囲まれていない表示形式では、24時間を超える時間が表示されません。
通常勤務時間・時間外・深夜残業・実働時間はそれぞれ数式を入力します。
(出勤時間セルをC3、退勤時間セルをD3、休憩時間セルをE3、通常勤務セルをF3、時間外セルをG3、深夜残業セルをH3、実働時間セルをI3として）
通常勤務時間（F3）に以下の式を入力
=IF(C3="","",(D3-MAX(C3,"9:00")- E3)-(G3 + H3))
所定労働開始時間が９時～という設定のため、９時より前の出勤時間を計算しないように、MAX関数で出勤時間と9:00を比較し遅い方（9:00）を表示しています。
9時より前の出勤時間も計算したい場合は、MAX関数を使わずそのまま出勤時間セルを参照させてください。
時間外（G3）に以下の式を入力
=MAX(MAX(D3,"18:00")-"18:00"),0)-H3
深夜残業（H3）に以下の式を入力
=MAX(MAX(D3,"22:00")-"22:00"),0)
１日の実働時間（I3）も必要であれば、上記３つの式を入れたセルF3・G3・H3を合計します。
=SUM(F3:H3)
F3～I3の式は末日までコピーし、下の行に１日から末日までの列ごとの時間の合計をSUM関数で出します。
勤務時間の合計が出たら、次に注意したいのは「時間の単位を数値（10進数）に変換する」です。
Excelでは時刻は24時間を１とするシリアル値で扱っており、勤務時間と時給から給与を計算する時に、時間の単位が〇時間〇分のままでは正しく計算することができません。
＝時間合計セル/ "1:0:0"
という式で、時間のシリアル値を数値に変換する必要があります。
（計算結果は数値になるため、セルの書式設定も数値に変更します）
ここまでできればあとは時給と時間を掛けて合計するだけ。
時間外（時給×1.25）や深夜割増分（時給×1.5）の計算もお忘れなく！
以上、気を付けたいポイントをまとめると、
・時給額が変わるため、通常勤務時間と残業は分けて表示させる
・セルの書式設定を確認する（時間の合計を表示するなら[h]:mmで！）
・時給と時間を掛け算する時は時間の単位を数値に変換する（書式設定は数値で！）
基本的なことだけご説明しましたが、職場によっては色々ルールがあると思います。
時間を特定の単位で切り捨て・切り上げ、遅刻や早退時間を引いて計算、など、関数で色々な調整ができますので、「もっとこうしたい！」というご希望がありましたら、お気軽にスタッフにお尋ねくださいね♪