こんにちは、Ryoです。
カードリーダーなどで集計された出勤／退勤打刻データから勤怠集計を自動で行いたいようなケースではHour/Minute関数やTimeSerial関数を活用して対応することができますので、簡易的なサンプルを作成してみました。今回はその内容について書いていきます。
1.サンプル概要
出勤時の打刻時間、退勤時の打刻時間が入力されていますので、そのデータから実働時間（就業時間）と残業時間の算出を行います。
この例では8:00－17:00が定時間で休憩は1時間、遅刻などは15分単位で切り上げており早退や残業時間などは15分単位で切り下げの条件で計算します。
実行結果は以下の通りです。
遅刻や早退、残業有無などを判定して計算し、その結果を「就業時間」「残業時間」にそれぞれ書込んでいます。表記は集計しやすいように15分→0.25の形に変えています。
出勤打刻時間や退勤打刻時間から上述の基準に準じて時刻を置き換えたものをイミディエイトウィンドウで表示すると以下のようになっており、この時刻を使って終業時間や残業などを算出しています。
以上がサンプル概要になります。
2.サンプルコード
◆Subプロシージャ：メインルーチン
Option Explicit
Dim Array1 As Variant
Dim i As Integer
Sub Sample1()
Dim totalcnt As Integer
Dim s_Time, e_Time, TotalTime, ovTime As Double
'セルA2からC6の範囲を配列格納
Array1 = ActiveSheet.Range("A2:C6")
'配列の1次側の総数をカウント
totalcnt = UBound(Array1, 1)
'データ数カウント分繰り返し処理を実施
For i = 1 To totalcnt
'始業打刻時間の判定と置換をfunc1()にて実施
s_Time = func1()
'退勤打刻時間の判定と置換をfunc2()にて実施
e_Time = func2()
'置換した退勤時間から出勤時間と休憩時間をマイナスし、
'就業時間Totalを算出(*24として15分=0.25とする)
TotalTime = (e_Time - s_Time - Array1(i, 3)) * 24
'残業時間も*24として15分=0.25とする
ovTime = ((e_Time - s_Time - Array1(i, 3)) - _
TimeSerial(8, 0, 0)) * 24
'セルD列にフォーマット形式
'（数値／小数第2位まで表示）指定
With Cells(i + 1, 4)
.NumberFormatLocal = "0.00"" h"""
'就業時間を書き込み
.Value = TotalTime
End With
'セルE列にフォーマット形式
'（数値／小数第2位まで表示）指定
With Cells(i + 1, 5)
.NumberFormatLocal = "0.00"" h"""
'残業時間が発生していれば書き込み
If ovTime > 0.01 Then
.Value = ovTime
Else
'残業なしの場合は空欄
.Value = ""
End If
End With
Next i
End Sub
◆Functionプロシージャ1（出勤打刻判定／置換）
Function func1() As Double
'＊＊出勤打刻時間を判定、置換する処理＊＊
Dim eng_hs, eng_ms, toA, tob As Integer
Dim DateAh As Double
'打刻された始業時間から"時"、"分"を抽出
eng_hs = Hour(Array1(i, 1))
eng_ms = Minute(Array1(i, 1))
'"時"、"分"判定用としての処理
toA = 8 - eng_hs
tob = 0 - eng_ms
'始業時間と規定時間、分をそれぞれ引いて定刻か遅刻かの判断と
'遅刻であれば時間と分（15分刻み）を置換する
Select Case toA
'打刻時間＝始業時間
Case Is = 0
If tob >= 0 Then
'規定時間の8:00を戻す
func1 = TimeSerial(8, 0, 0)
Else '時間が同一でも分が設定より多い（遅刻）の場合
Select Case Abs(tob)
Case Is <= 15
func1 = TimeSerial(8, 15, 0)
Case Is <= 30
func1 = TimeSerial(8, 30, 0)
Case Is <= 45
func1 = TimeSerial(8, 45, 0)
Case Is <= 59
func1 = TimeSerial(9, 0, 0)
End Select
End If
'打刻時間が設定時間よりも大きい（時間が早い）
Case Is > 0 '規定時間の8:00を戻す
func1 = TimeSerial(8, 0, 0)
'打刻時間が設定時間よりも小さい（時間が遅い）
Case Is < 0 '遅れている時間を設定時間に加算
DateAh = DateAdd("h", Abs(toA), TimeSerial(8, 0, 0))
Select Case Abs(tob) '分は15分刻みで判定して置換
Case Is <= 15
func1 = DateAdd("n", 15, DateAh)
Case Is <= 30
func1 = DateAdd("n", 30, DateAh)
Case Is <= 45
func1 = DateAdd("n", 45, DateAh)
Case Is <= 59
func1 = DateAdd("n", 60, DateAh)
End Select
End Select
'↓↓イミディエイトウィンドウで確認したい場合に使用
'Debug.Print Format(func1, "hh:mm:ss")
End Function
◆Functionプロシージャ2（退勤打刻判定／置換）
Function func2() As Double
'＊＊退勤打刻時間を判定、置換する処理＊＊
Dim eng_he, eng_me, toA, tob As Integer
Dim DateAh As Double
'打刻された退勤時間から"時"、"分"を抽出
eng_he = Hour(Array1(i, 2))
eng_me = Minute(Array1(i, 2))
'"時"、"分"判定用としての処理
toA = 17 - eng_he
tob = 0 - eng_me
'退勤時間と規定時間、分をそれぞれ引いて早退、定時退社
'または残業かの判断と定時以外であれば時間と分（15分刻み）を置換する
Select Case toA
'打刻時間＝定時時間
Case Is = 0
If tob >= 0 Then
'規定時間の17:00を戻す
func2 = TimeSerial(17, 0, 0)
Else '時間が同一でも分が設定より多い=残業が見込まれる場合
Select Case Abs(tob)
Case Is < 15
func2 = TimeSerial(17, 0, 0)
Case Is < 30
func2 = TimeSerial(17, 15, 0)
Case Is < 45
func2 = TimeSerial(17, 30, 0)
Case Is < 59
func2 = TimeSerial(17, 45, 0)
End Select
End If
'打刻時間が規定時間よりも小さい（早退）
Case Is > 0
'規定の17:00から退勤時間に合わせる
DateAh = DateAdd("h", toA * -1, TimeSerial(17, 0, 0))
Select Case Abs(tob) '分は15分刻みで判定して置換
Case Is < 15
func2 = DateAdd("n", 0, DateAh)
Case Is < 30
func2 = DateAdd("n", 15, DateAh)
Case Is < 45
func2 = DateAdd("n", 30, DateAh)
Case Is < 59
func2 = DateAdd("n", 45, DateAh)
End Select
'打刻時間が規定時間よりも大きい（残業）
Case Is < 0 '規定の17:00から退勤時間に合わせる
DateAh = DateAdd("h", Abs(toA), TimeSerial(17, 0, 0))
Select Case Abs(tob) '分は15分刻みで判定して置換
Case Is < 15
func2 = DateAdd("n", 0, DateAh)
Case Is < 30
func2 = DateAdd("n", 15, DateAh)
Case Is < 45
func2 = DateAdd("n", 30, DateAh)
Case Is < 59
func2 = DateAdd("n", 45, DateAh)
End Select
End Select
'↓↓イミディエイトウィンドウで確認したい場合に使用
'Debug.Print Format(func2, "hh:mm:ss")
End Function
先ず初めにセルA2からC6のデータを配列に格納し、格納した配列数＝データ数をカウントしてループ処理を行います。
始業や終業の打刻時間から基準に準じて判定や置換をそれぞれのファンクションプロシージャで処理し値をメーンルーチンへ戻します。func1()は出勤時間／func2()は退勤時間について処理するものです。
func1()の処理ではHour関数／Minute関数を利用して打刻された出勤時間から”時”と”分”を取り出しています。
構文：Hour/Minute/Second(time) シリアル値や時刻を表す文字列から取得
取得した「時間」「分」と打刻時間を比較・演算し、TimeSerial関数で適正時刻を設定してメインルーチンへ値を戻します。
構文：TimeSerial（hour,minute,second）
引数に指定できる値は「hour:0～23，minute：0～59, second：0～59」になりますが、引数minuteに60を指定すると1繰り上がってhourの値に1が加算される処理になります。
func2()の処理についてはfunc1()と流れはほぼ同様ですが、こちらは早退など退勤打刻や残業についてなので15分単位での切り下げとなるよう処理を行い、メインルーチンへ値を戻しています。
あとは受け取った値をメインルーチン側で処理していますが、就業時間や残業時間表記を数値に置換したいので、受け取った値に24を乗じてから変数へ格納します。
書込むセルの表示形式を指定（数値：小数第2位表示＋”h”付加）し、書込む処理を行って終了となります。
3.まとめ
今回はTimeSerial関数の部分だけ触れるような簡単な記事を書こうと思っていたのですが、最近は出退勤絡みのデータを扱うこともあるので簡単なサンプルを思うまま書いてみました。
やはり時刻を扱うような処理はシリアル値で演算するとうまく処理できないこともあるので、Hour/Minute/second/DateAdd/TimeSerial関数を有効利用した方が簡単で扱いやすいと思います。
おそらくもっと簡潔に処理できると思うので、また改めて考えてみて何かあれば更新していきます。
以上、TimeSerial関数などを利用した勤怠集計サンプルでした！今回の記事が何かの参考になれば幸いです。
Ryo
