2016/11/30 公開

ExcelのTIME関数、SUMIF関数、DAY関数を使った残業時間と残業代の計算方法。

Pocket

Excelの活用において、「時刻の計算は難しい」というイメージを持つ人が多くいます。それは、計算を行う関数だけでなく、表示形式の統一など多くの関数が求められているためです。

今回は勤怠管理表をもとにしながら、複数の関数を用いて社員の残業時間と残業代を求める方法を解説します!

1:TIME関数を使い、時間外勤務時間を求める。
2:TEXT関数とSUMIF関数で勤怠表から休日の勤務時間を求める。
3:勤務時間をもとに、HOUR関数とMINUTE関数とDAY関数を使って残業代を求める。

1:TIME関数を使い、時間外勤務時間を求める。

以下の勤怠管理表を使い、勤務時間の合計から時間外勤務時間を求めていきます。1日の所定労働時間は8時間、月間の労働日数は20日とします。

overtime1
まずはTIME関数を使い、月間所定労働時間を計算します。1日の所定労働時間は8時間であるため、単純に当月の所定労働日数の20日を掛ければ結果は出るように思えるかもしれません。しかし、分単位で記入がされている勤務時間と計算を行うため、分単位で表示ができるようにする必要があります。

計算式は以下のようになります。
overtime2-e1472650467458

所定労働時間は8時間であるため、括弧の中の時間にあたる箇所を8にして計算を行います。

overtime3

すると、分の単位まで計算した時間数が表示されました。次はそれぞれの勤務時間から、月間所定労働時間を引いて時間外勤務時間を求めます。

この時、C2セルの数値を固定させるため、それぞれの勤務時間を$C$2と入力し、計算を行いましょう。

overtime4

こうすることで、それぞれの社員の時間外勤務時間を求めることができました。

2:TEXT関数とSUMIF関数で勤怠表から休日の勤務時間を求める。

続いて、個人の勤怠表をもとに休日の勤務時間を求めましょう。

overtime5

休日(土曜日、日曜日)出勤の勤務時間を求める前に、TEXT関数で日付を曜日形式の文字列に変換しましょう。数式は以下となります。

overtime6-e1472650515163

日付を曜日形式の文字列に変換できたら、SUMIF関数で土曜日、日曜日の勤務時間を抜き出し、合計時間を算出します。

このSUMIF関数は指定した範囲内を検索し、条件に合う行の数値を合計する関数となっています。しかし、2つの曜日を一度に指定することができないため、それぞれ個別に計算していきます。

overtime7

SUMIF関数で計算を行った結果、土曜と日曜出勤分の勤務時間を求められました。

overtime8

勤務時間をもとに、HOUR関数とMINUTE関数とDAY関数を使って残業代を求める。

overtime9

休日時間外と平日時間外の残業代を求めましょう。休日時間外勤務の1時間あたりの単価は2,800円。平日時間外勤務の1時間あたりの単価は2,300円とします。

ここで使用するのはHOUR関数、MINUTE関数、DAY関数の3種類。それぞれ時刻データから「時間」、「分」、「日数」を取り出す関数です。勤務時間は「時:分」で表されているので、「時」はHOUR関数、「分」はMINUTE関数で数値として取り出します。この際、「分」は「時」に換算して計算を行うため、60で割ります。

早速計算を始めていきましょう。数式は以下の通りになります。

overtime10

一見こちらで問題なく計算はできそうですが、HOUR関数は1日のうちの時間を取り出す関数です。24時間以上になる残業の分を取り出せない問題に向け、DAY関数を使用します。さらにここに24時間をかけ、時間に換算しなければいけません。

overtime11

もしも残業時間が24時間未満の場合であっても、DAY関数で計算された値は0となります。それぞれの関数で取り出した時間に1時間当たりの時間外単価をかけることで、残業時間を算出することができました。

overtime12

難しい時間の計算も、関数で。

Excelでの時間の計算をするのであれば、単位や表示を合わせる必要があります。そんな時には最後にご紹介したDAY関数やMINUTE関数を使い、「時間」に基準を合わせましょう。

さらに企業によって異なる出社時間や退社時間、1時間あたりの単価について変更があれば即座に確認、変更を行っていきましょう。もしもミスがあれば従業員と企業との間でトラブルに発展しやすい給与。今一度社内のルールと計算方法を確認することが求められています。

■Excel活用術バックナンバー
Vlookup関数とエラー「#N/A」の回避方法を伝授!人材管理に役立つExcel Vlookup術[vol.1]
MATCH関数とVLOOKUP関数の合わせ技!人材管理に役立つEXCEL VLOOKUP術[vol.2]
INDEX関数とVLOOKUP関数を組み合わせて昇給額を算出!EXCEL VLOOKUP術[Vol.3]
VLOOKUP関数で同一の検索値が複数ある場合のデータ抽出術!EXCEL VLOOKUP術[Vol.4]前編
重複する検索値はCOUNTIF関数で個別化しよう!EXCEL VLOOKUP術[Vol.4]後編
VLOOKUP、COUNTIF、MATCH関数を使って勤務管理を効率化!EXCEL VLOOKUP術[Vol.5]
【TODAY・DATEDIF・CONCATENATE】エクセル関数を使った社員名簿・社員台帳の作り方
【REPT関数、MOD関数、ROW関数】エクセル関数を使った見やすい表の作り方
【エクセルでグラフ作成】会社の人事データを整理するためのグラフの作り方

運営会社 | Copyright © kaonavi, inc. All Rights Reserved.