開発ブログ

雑多に書きます

Excelの勤務時間計算で24時間が消える問題


「あ…ありのまま 今 起こったことを話すぜ！」
「出勤時間を計算して合計144時間になったが、セルの値をコピーしたら120時間になっていたんだ」
「な… 何を言っているのか わからねーと思うが、おれも何をされたのかわからなかった… 頭がどうにかなりそうだった…」
「浮動小数点の誤差とかそんなチャチなもんじゃあ 断じてねえ… もっと恐ろしいものの片鱗を味わったぜ…」

…つまりですね、弊社ではスタッフの勤務時間の計算をエクセルで行っていたのですが何故か計算結果が24時間分減っている事例が起きていました。 で、原因は良くある「浮動小数点の誤差」が関連するのですが それでも24時間分が無くなる仕組みが 明確にわかっていなかったので、 その原因を今回詳細に調べました。

エクセル内部の仕組み

知っている人も多いと思いますが、Excelの日付時間はシリアル値で保存されています。 シリアル値は整数部が1900年01月01日を1を起点とした日付、少数部が1.0を24時間とした数値で時間を表しています。 デバッガで確認するとセル内部の型としては Variant/Double が使用されています。

これをエクセルで表示すると以下のように表示されます。

問題となる例

問題は勤務時間の総計セルを別のセルにコピーした際に起きていました。

同じ状態を再現するために総計したA1セルをVBAで別のセルにコピーしてそれぞれの書式で表示しています。

  • プログラム
    Sub CopyCell()
        formats = Array("[h]:mm", "yyyy/mm/dd hh:mm:ss", "0.000000000000000000")
        With ThisWorkbook.ActiveSheet
            Dim sumDate As Date
            Dim sumDble As Double
            Dim sumVari As Variant
            sumDate = .Range("A1").Value
            sumDble = .Range("A1").Value
            sumVari = .Range("A1").Value

            For i = LBound(formats) To UBound(formats)
                .Range(.Cells(2, i + 4), .Cells(5, i + 4)).NumberFormatLocal = formats(i)
                .Cells(2, i + 4).Value = sumDate
                .Cells(3, i + 4).Value = sumDble
                .Cells(4, i + 4).Value = sumVari
                .Cells(5, i + 4).Value = .Range("A1").Value
            Next
        End With
    End Sub

見てわかる通り、セルの値を一旦Date型にコピーすると24時間分減っています。今回はこのパターンで発生していました。

一見、型を変えてやれば上手くいくように見えて実はそうではありません。 見た目上、正しく表示されているDouble型の時間のセルをダブルクリックすると日付表示になりますが この値は「1900/1/5 00:00:00」と表示され、日付の値と一致しません。

この状態でセルを移動させると値が変化します。

「なんじゃそりゃーーーーーーーーーー！！」

原因を探る

勤務時間を計算する場合は「時間」で計算するのですが、「時間」はシリアル値の少数部であるため計算上の誤差が生じます。 ただ本来この誤差は無視できるほどの小さいので（あっても±1秒差が起きる程度）問題が起きるような事は本来考えにくいのですが、 型変換をした際の誤差が何かしらの影響を及ぼしていたと考えていました。

しかし、24時間の変化はそれだけでは説明がつきません。

まずは誤差がどの程度あったかを調べます。 しかし誤差は小さすぎるためにセルの書式設定で小数点をいくら表示しても現れませんし、VBAデバッガでも表示されませんでした。 少数部のみを取得するプログラムを挿入して確認した所、少数部は0.999999999999998であることがわかりました。

つまり 144時間 -> 120時間になる数値は 5.999999999999998 という事になります。

次に現象が起きる誤差範囲を調べたところ、少数部が1 - (１/ (24 * 60 * 60 * 2))の境界で起きる事がわかりました。 つまり、少数部を四捨五入して数値化する際に23:59:59を超えて00:00:00になる場合にこの現象が発生します。

結論

どうやら「シリアル値を日付文字列にするアルゴリズムが間違っている」様です。 文字列を生成する際に整数部（日付）と少数部（時間）を分けて計算しているようで、 少数部（時間）が23時間59分59秒を超えた場合に時間は00:00:00になりますが、 その際に整数部（日付）への繰り上がりが必要なのにこれを行っていない為に1日のずれが起きています。

つまり、この文字列変換を通す場合に1日のずれが発生します。

  • 該当セルをクリックして式を見ると文字列変換された日付表示が表示されます。この値は正しくありません。
  • ダブルクリック後すると、文字列変換された日付表示がセル上に表示されます。この値は正しくありません。
  • ダブルクリック後にカーソルを動かすと、その日付表示で再入力されるために数値が書き換わります。
  • ダブルクリック後に[Esc]キーで抜けた場合は、再入力されないため変化が起きません。
  • Double -> Date 型に変換の場合に値が変化する理由は、Double -> 文字列変換 -> Date値という手順で変換している為と思われます。

対処法

対処法としてはシリアル値自体を文字列変換が起きても正しい値が表示される補正をする事です。 具体的には秒数側が 23:59:59.5 を超えた場合に秒数を0にして整数値に繰り上げさせます。

シリアル値を下記の関数に通すことで問題は発生しなくなることを確認しました。

Function RoundSec(d As Double) As Double
    RoundSec = Round(d * 86400#) / 86400#   ' 86400 = 24 * 60 * 60
End Function

検証環境

Microsoft Windows 10 Pro for Workstations 10.0.19045 ビルド 19045 Microsoft® Excel® for Microsoft 365 MSO (バージョン 2302 ビルド 16.0.16130.20186) 64 ビット

テスト用エクセル https://1drv.ms/u/s!Aj1C4e43DYFurFZtVvMuGmHvY905?e=OI17yt