今回もエクセルの神髄様の Twitter 企画「VBA100本ノック」に便乗させていただきます。 今回はその91本目への解答です。Date 型の使い方もまとめました。
VBA100本ノック 91本目：時間計算（残業時間の月間合計）
#VBA100本ノック 91本目
— エクセルの神髄 (@yamaoka_ss) 2021年2月19日
「勤怠」に複数月の勤怠データが入っています。
9:00～18:00の休憩1hの実動8hです。
id・月ごとに残業時間を算出し「残業」に一覧出力。
残業：単純に1日8hを超える時間数。ただし9時前出勤は9時とする。
日々1分単位、月間30分単位で切り捨て。
※時間計算の練習問題です。 pic.twitter.com/ROf5vsuL7N
問題ページ
方針
実務経験者ならではの実践的かつ実戦的なお題ですね。
今回のお題のポイントは「時間計算」と「複数キーによる集計」です。
日付や時刻の計算はプログラミングの必須課題ですが、個人的にはこれまで VBA で日付や時間を扱った経験がほとんどありません。 これを機に、VBA での時刻の扱いをマスターしたいと思います。
調べると VBA での時間計算にも、いくつかの流儀があるようですが、本解答ではあくまで基本にのっとり日時を表す組込型の「Date 型」を中心に使う方針とします。
集計の方は、ロジックを組まなくても Excel が持つ高度な集計機能を利用する方法がありそうな気がしますが、 今回は時間計算を重点的に集中したいので、凝ったことせずにシンプルなループと Scripting.Dictionary を使ったロジックとします。
また実業務開発を想定して、動きだけでなく、レビューやテスト、変更修正のしやすさなどもできるだけ意識して取り組みたいと思います。
解答：Date 型を使ってみる
Option Explicit Const 始業時刻 = "9:00 AM" Const 労働時間 = "8:00" Const 休憩時間 = "1:00" Const 残業単位 = "0:30" Private Enum 勤怠 id = 1 日付 出勤時刻 退勤時刻 End Enum ' 日次残業計算 ' 第一引数のdtは未使用。休日残業対応などへの予約。 Function calcNichijiZangyo(dt As Date, syukkin As Date, taikin As Date) As Date Dim kinmu As Date Dim shigyo As Date Dim jikkin As Date Dim zangyo As Date kinmu = CDate(労働時間) + CDate(休憩時間) jikkin = taikin - max(syukkin, CDate(始業時刻)) ' 始業前は勤務時間外 If jikkin > kinmu Then zangyo = jikkin - kinmu zangyo = truncTime(zangyo, CDate("0:01:00")) ' 秒切り捨て Else zangyo = 0 ' 繰越相殺なし End If calcNichijiZangyo = zangyo End Function ' 月次残業計算 Function calcGetsujiZangyo(totalZangyo As Date) As Date calcGetsujiZangyo = truncTime(totalZangyo, CDate(残業単位)) ' 残業単位で切り捨て End Function Sub VBA100_091() Dim kintaiData As Range Set kintaiData = Worksheets("勤怠").Range("A1").CurrentRegion Set kintaiData = Intersect(kintaiData, kintaiData.Offset(1)) Dim zangyoReport As Range Set zangyoReport = Worksheets("残業").Range("A1").CurrentRegion Set zangyoReport = zangyoReport.Offset(1) zangyoReport.ClearContents Dim getsujiZangyo As Object Dim getsuji As Variant Dim zangyo As Double Set getsujiZangyo = CreateObject("Scripting.Dictionary") ' 各ID月次ごとに残業時間を集計する Dim rec As Range For Each rec In kintaiData.Rows getsuji = rec.Cells(勤怠.id) & vbTab & Format(rec.Cells(勤怠.日付), "yyyymm") zangyo = calcNichijiZangyo(rec.Cells(勤怠.日付), rec.Cells(勤怠.出勤時刻), rec.Cells(勤怠.退勤時刻)) getsujiZangyo(getsuji) = getsujiZangyo(getsuji) + zangyo Next ' 有効な月次残業時間を算定する For Each getsuji In getsujiZangyo.Keys zangyo = calcGetsujiZangyo(getsujiZangyo(getsuji)) getsujiZangyo(getsuji) = zangyo Next ' 出力 Dim idYM As Variant Dim cur As Range Set cur = zangyoReport.Rows(1).Resize(, 3) For Each getsuji In getsujiZangyo.Keys idYM = Split(getsuji, vbTab) zangyo = getsujiZangyo(getsuji) cur = Array(idYM(0), idYM(1), zangyo) Set cur = cur.Offset(1) Next End Sub Private Function max(a As Variant, b As Variant) As Variant max = IIf(a >= b, a, b) End Function ' 時刻切り捨て計算(正のDateのみ) Private Function truncTime(ByVal a As Date, ByVal b As Date) As Date Dim sa As Long: sa = DateDiff("s", 0, a) Dim sb As Long: sb = DateDiff("s", 0, b) truncTime = DateAdd("s", (sa \ sb) * sb, 0) End Function
考察
VBAでの日時計算は面倒くさいのだろうなとは思っていましたが、想像以上にクセのある世界でした。
Date 型について調べていたら、だいぶ深堀りしすぎてしまいました。
Date 型について
VBA で時刻・時間を扱うには Date 型を使います。
Date 型は日時（DateTime）を保持する型ですが、 時刻（Time）のみの型や時間（期間/Interval）を表す型は用意されていないので、それらも Date 型で扱うことになります。
Date 型の内部表現はシリアル値と呼ばれる Double 値です。
シリアル値の整数部は基準日（1899/12/31）からの日数を、小数部は時刻として扱います。 時刻は 1.0 を24時間とした割合に対応します。 時刻のみを扱う場合、基本的にシリアル値は 1.0 未満で小数部のみ（整数部0）を使います。
’ 日時とシリアル値 ? CDbl("2021/01/01 12:34:56") 44197.5242592593 ? CDate(44197.5242592593) 2021/01/01 12:34:56 ? CDate(1) 1899/12/31 ? CDate(0) 0:00:00 ? CDate(0.5) 12:00:00 ' 正午 ? CDate(1.5) 1899/12/31 12:00:00 ' 1 秒のシリアル値 ? CDbl("00:00:01") 1.15740740740741E-05 ' = 0.0000115740740740741 ? CDate(0.00001157407) 0:00:01
残業集計値などは24時間を超えた値になりますが、これを Date 型で扱うと 1.0 より大きなシリアル値となり、表示すると日付が出てしまいます。 書式を時刻のみ（hh:mm:ss）にしても24時間以上は周回した時間の表示になってしまいます。
その場合、Excel のセル書式なら、時間(h)の書式を [h] や [hh] とすれば24時間を超える時間を表示することができます。
ところが、VBA では Format 関数がこの書式をサポートしません。 その代わりに WorksheetFunction.Text メソッドを呼び出します。
? CDate(1.5) 1899/12/31 12:00:00 ? WorksheetFunction.Text(1.5, "[hh]:mm:ss") 36:00:00 ? Format(1.5, "hh:mm:ss") ' 24時間で周回する 12:00:00 ? Format(1.5, "[hh]:mm:ss") ' [hh]を使うと壊れる :12:00 ? Format(1.5, "ttttt") 12:00:00
Date 型の生成と変換
Date 型の時刻のリテラル表現は、コロン区切りの時刻形式を # で囲み、#12:34:56# あるいは秒を省略して #12:34# ように書きます。
24 時間以上を指定するとエラーになります。
VBA 的にはそれだけでいいはずなのですが、VBE エディタのおせっかいによって勝手に正規化されます。
#12:34:56# ⇒ #12:34:56 AM#
#13:00:00# ⇒ #1:00:00 PM#
#00:00:00# ⇒ #12:00:00 AM#
#12:34# ⇒ #12:34:00 PM#
#24:00:00# ⇒ エラー
これでは Date 型変数の初期化や時刻定数の定義に時刻リテラルを使いたくても、おせっかいにより意図が失われ非常に迷惑です。
リテラルではなく、時刻を書式化した文字列の方が断然使い勝手がいいです。
時刻の文字列は CDate 関数や TimeValue 関数によって Date 型に変換できます。
また、Date 値を引数に受け取る関数は基本的に、文字列形式のままでも受け付けてくれます。
? CDate("12:34:56") 12:34:56 ? CDate("2:00:01") ? 2:00:01 ? CDate("2:00:01 PM") ' 14:00:01 ' 12時間表記 14:00:01 ? CDate("12:34") ' 秒を省略できる 12:34:00 ? CDate("1:2") 1:02:00 ? CDate("00:00:00") 0:00:00 ? TimeValue("12:34:56") 12:34:56 ? DateDiff("n", "9:00:00", "19:32:41") ' 文字列形式でもOK 632
ただし、関数に24時以上の時刻を与えるとと実行時エラーになるので注意が必要です。
? CDate("23:59:59") 23:59:59 ? CDate("24:00:00") ' エラーになる
時刻の場合はこれでもいいのですが、24時間以上の時間を初期値や定数としたいときには困りものです。
TimeSerial 関数を使えば、24時間以上の Date 値を生成できます。
TimeSerial 関数は整数で指定された時・分・秒からなる Date 型の値を生成するものです。
? TimeSerial(12, 34, 56) ' 時・分・秒を指定 12:34:56 ? TimeSerial(24, 00, 00) 1899/12/31 ' = 24:00:00 ? TimeSerial(65, 43, 21) 1900/01/01 17:43:21 ' = 65:43:21
また、Date 型は数値型の一種なので、他の数値型との間で暗黙の型変換が行われます。 1.0 以上の Double 値を Date 型に変換すれば、24時間以上の時間を用意できます。
Dim dt As Date dt = 1.5 ' 36:00:00 の生シリアル値 Dim i As Long i = Now ' 44250 ' 整数に丸め
? TypeName (1.5 + #12:34:56#) Date ? TypeName (1 + #12:34:56#) Date
CDate 関数を使えば、明示的に整数値や Double 値を Date 型に変換することができます。
? CDate(1.5) 1899/12/31 12:00:00 ? CDate(44250) 2021/02/23
以下の図に Date 型への生成と別データ型からの相互変換の方法をまとめました。
Date 型の演算
Date 型は数値型の一種なので、四則演算子による計算が可能です。
時間の計算でも Date 型のまま加算・減算して整合します。
Date 値同士の積や商も計算できますが、結果にシリアル値としての意味はなく、型も Double になります。
? #18:00# + #2:00# 20:00:00 ? #18:00# - #2:00# 0.666666666666667 ' = 16:00:00 ? #1:00# / #0:15# 4 ? #0.15# * 4 4.16666666666667E-02 ' = 1:00:00
注意したいのは計算結果が負になる場合です。
Excel のセルは負の時刻を扱えません。
Date 型は、負のシリアル値でも保持できて計算も可能ですが、負の時刻は書式で表現できません。
Date 型の計算では計算結果が負にならないよう注意した方がいいでしょう。
dt = #1:00:00 AM# - #1:00:05 AM# ' ??:00:55 をなんとなく期待 Debug.Print Format(dt, "ttttt") ' 0:00:05 エラーにはならず、絶対値の時刻が表示される Debug.Print (dt < 0) ' True シリアル値は負（-8.33333333333333E-02）になっている
ところで、2進数の浮動小数点で表現されている時刻はあくまで近似値にしかならないので、計算に際してはどうしても誤差が生じます。
できれば、時・分・秒などの整数ベースで計算したいところです。
VBA にはそのための Date 操作関数がいくつか用意されています。
? DateAdd("h", -9, "12:34:56") ' 9時間引く 3:34:56 ? DateDiff("n", "9:00:00 AM", "9:34:56 PM") ' 分単位の時間差（秒を位切り捨ててからの差） 754 ? Hour("12:34:56") * 60 * 60 + Minute("12:34:56") * 60 + Second("12:34:56") ' 秒単位に変換 45296 ' Hour 関数は 24以上でエラーになるので注意
便利なのですが、実際に時間計算のロジックに組み込んでみるとどうも煩雑で分かりづらくなります。
いっそのこと、シリアル値をいったん秒単位などの時刻としてキリのいい整数に変換し、整数値として計算したいところです。
理屈ではシリアル値に1日の秒数（86400）を掛けて整数に丸めれば秒単位の整数に変換できます。 それでも厳密には丸め方式などが微妙な誤差として影響しそうなのが悩ましいです。
? CLng(#00:00:01# * 86400) ' 一番近い偶数への丸め（いわゆる銀行丸め） 1 ? Int(#00:00:01# * 86400) ' 小数部切り捨て 0 ? WorksheetFunction.Round(#00:00:01# * 86400, 0) ' 四捨五入 1
どうしても誤差なしで Date 値と整数値を相互変換したければ、DateDiff 関数と DateAdd 関数を使うのがよいでしょう。
? DateDiff("s", 0, "12:34:56") ' 秒単位整数への変換 45296 ? DateAdd("s", 45296, 0) ' 秒単位整数をDate型に戻す 12:34:56 ? DateDiff("n", 0, "12:34:56") ' 分単位整数への変換（秒単位切り捨て） 754 ? DateAdd("n", 754, 0) ' 分単位整数をDate型に戻す 12:34:00
Date 型の切り捨て
秒や分での切り捨ては DateDiff 関数で簡単にできるのですが、30 分単位で切り捨てるとなるとそうもいきません。
Date 型は Double 型に暗黙的に変換されるので、WorksheetFunction.Floor() などの数値用関数がそのまま使えます。
? CDate(WorksheetFunction.Floor(#12:34:56#, #00:30:00#)) ' 30分単位で切り捨て 12:30:00 ? CDate(WorksheetFunction.Floor_Math(#12:34:56#, #00:30:00#)) ' Excel 2013 以降の推奨 12:30:00
ただ、これをそのまま使ってもいいのですが、時刻に戻した時にすべて結果で期待値にぴったり合うことが保証できるのかは、何とも言えません。
それを検証する時間も意味もありませんので、ここは自前で切り捨て処理を実装した方が手っ取り早くて安心感も得られます。
もっとも、残業時間の計算にこれほどの精度とこだわりが必要なのかと言われれば、それは出題者さんに聞いてくださいとしか。
参考資料
- Date データ型 | Microsoft Docs
（日本語訳が壊れているのを何とかしてほしいです） - EXCEL VBA 他 時間計算 時間シリアル 十進数変換法 - Qiita
- VBAの小数以下の演算誤差について｜VBA技術解説
- データ型変換関数 (VBA) | Microsoft Docs
- My First BillG Review – Joel on Software
シリアル値の日付がなぜ 1900/01/01 からではなく 1899/12/31 から始まるのか経緯が分かります。 VBA プログラマならぜひとも読んでおきたい非常に面白い有名なブログ記事です。日本語訳がなくなってる…
関連記事
なし
