労働時間を求める関数
http://q.hatena.ne.jp/1259678627
以前に作成したユーザー定義関数は、勘違いの部分があったので大幅に変更しました。
作成したのは4つのユーザー定義関数です。
定時時間・・・所定労働時間と法定内残業時間の合計です。最大が8時間であり時給×1となります。
残業時間・・・法定外残業時間です。時給×1.25となります。
定時深夜時間・・・定時時間内の深夜時間です。時給×1.25となります。
残業深夜時間・・・残業時間内の深夜時間です。時給×1.5となります。
※深夜時間とは23時から5時になります。
例えば実際の時間給での給与の計算では次の式になります。
給与 = ((定時時間−定時深夜時間)+(残業時間−残業深夜時間+定時深夜時間)×1.25+残業深夜時間×1.5)×時給
もしくは
給与 = (定持時間+残業時間×1.25+定時深夜時間×0.25+残業深夜時間×0.25) × 時給
Function 定時時間(r1 As Range, r2 As Range) As Double Dim res As Double res = 0 If r1.Value <> "" And r2.Value <> "" Then res = jobTime(r1, r2) If res >= 8 Then res = 8 End If End If 定時時間 = res End Function Function 残業時間(r1 As Range, r2 As Range) As Double Dim res As Double res = 0 If r1.Value <> "" And r2.Value <> "" Then res = jobTime(r1, r2) If res >= 8 Then res = res - 8 Else res = 0 End If End If 残業時間 = res End Function Function 定時深夜時間(r1 As Range, r2 As Range) As Double Dim res As Double Dim ST As Double Dim ET As Double res = 0 If r1.Value <> "" And r2.Value <> "" Then ST = r1.Value * 24 If r1.Value < r2.Value Then ET = r2.Value * 24 Else ET = r2.Value * 24 + 24 End If Select Case ST Case 0 To 5 Select Case ET Case 0 To 5 res = ET - ST Case 5 To 48 res = 5 - ST End Select Case 5 To 14 res = 0 Case 14 To 21 Select Case ET Case 14 To 22 res = 0 Case 22 To 29 If ET - ST >= 8 Then res = ST + 8 - 22 Else res = ET - 22 End If Case 29 To 48 res = ST + 8 - 22 End Select Case 21 To 22 Select Case ET Case 21 To 22 res = 0 Case 22 To 29 res = ET - 22 Case 29 To 48 res = 7 End Select Case 22 To 24 Select Case ET Case 22 To 29 res = ET - ST Case 29 To 48 res = 29 - ST End Select End Select End If 定時深夜時間 = res End Function Function 残業深夜時間(r1 As Range, r2 As Range) As Double Dim res As Double Dim ST As Double Dim ET As Double res = 0 If r1.Value <> "" And r2.Value <> "" Then ST = r1.Value * 24 If r1.Value < r2.Value Then ET = r2.Value * 24 Else ET = r2.Value * 24 + 24 End If Select Case ST Case 0 To 14 Select Case ET Case 0 To 22 res = 0 Case 22 To 29 res = ET - 22 Case 29 To 46 res = 7 Case 46 To 48 res = 7 + ET - 46 End Select Case 14 To 21 Select Case ET Case 14 To 22 res = 0 Case 22 To 29 If ST + 8 < ET Then res = ET - ST - 8 Else res = 0 End If Case 29 To 46 res = 29 - ST - 8 Case 46 To 48 res = 29 - ST - 8 + ET - 46 End Select Case 21 To 24 Select Case ET Case 21 To 46 res = 0 Case 46 To 48 res = ET - 46 End Select End Select End If 残業深夜時間 = res End Function Function jobTime(r1 As Range, r2 As Range) As Double Dim res As Double Dim ST As Double Dim ET As Double ST = r1.Value * 24 If r1.Value < r2.Value Then ET = r2.Value * 24 Else ET = r2.Value * 24 + 24 End If Select Case ST Case 0 To 12 Select Case ET Case 0 To 12 res = ET - ST Case 12 To 13 res = 12 - ST Case 13 To 36 res = ET - ST - 1 Case 36 To 37 res = 12 - ST + 23 Case 37 To 48 res = ET - ST - 2 End Select Case 12 To 13 Select Case ET Case 12 To 13 res = 0 Case 13 To 36 res = ET - 13 Case 36 To 37 res = 23 Case 37 To 48 res = 23 + ET - 37 End Select Case 13 To 24 Select Case ET Case 13 To 36 res = ET - ST Case 36 To 37 res = 36 - ST Case 37 To 48 res = ET - ST - 1 End Select End Select jobTime = res End Function
使い方は、数式に次のように式を入れて使います。
=定時時間(始業時間のセル,終業時間のセル)
=残業時間(始業時間のセル,終業時間のセル)
=定時深夜時間(始業時間のセル,終業時間のセル)
=残業深夜時間(始業時間のセル,終業時間のセル)