次の方法で共有

[Excel VBA] 時間の計算で最終的に 48:00 になる場合におかしな動作をすることがある

質問

2020年10月16日金曜日 6:51

Excel か Excel VBA の不具合っぽいのですが以下のような条件下の時に時間がおかしくなります。

  • 計算結果が 48:00 (Excel 上で 1900/1/2、VBA 上で 1900/01/01、シリアル値 2 の値) になる
  • 計算するときに片方の時間が 30 分単位 (例 CDate("1899/12/31 23:30:00") )
  • 足し算 (引き算では再現せず)

上記の値を Excel のセルに代入すると 24:00 (シリアル値 1) になってしまいます。

例として以下のサンプルを書きました。

Sub test()
    Range("b1:e2").NumberFormatLocal = "[h]:mm"                                     ' 表示形式を変更

    Range("a1").Value = "正常でない例"
    Range("a2").Value = "正常な例"

    Range("b2").Value = CDate("1900/01/01")                                         ' 48:00 (正常)
    Range("c1").Value = CDate("1899/12/31 23:30") ; CDate("0:30")                   ' 24:00 (値がおかしい)
    Range("c2").Value = CDate("1899/12/31 23:00") ; CDate("1:00")                   ' 48:00 (正常、30 分単位でなければ発生しない)
    Range("d1").Value = DateAdd("n", 210, "1899/12/31 20:30")                       ' 24:00 (値がおかしい)
    Range("d2").Value = DateAdd("n", 150, "1899/12/31 21:30")                       ' 24:00 (30 分単位でも正常なときがある)
    Range("e1").Value = CDate("1899/12/31 23:30") ; CDate("0:15") ; CDate("0:15")   ' (値がおかしい、15 分を 2 つ足しても再現する)
    Range("e2").Value = DateAdd("n", -30, "1900/1/1 0:30")                          ' 48:00 (引き算の場合は正常なもよう)

    ' おまけ
    Debug.Print CDbl(CDate("1900/01/01"))                                           ' 2
    Debug.Print CDbl(DateAdd("n", 30, "1899/12/31 23:30"))                          ' 2
    Debug.Print CDate("1900/01/01") = DateAdd("n", 30, "1899/12/31 23:30")          ' False
    Debug.Print CDate("1900/01/01") = 2                                             ' True
    Debug.Print DateAdd("n", 30, "1899/12/31 23:30") = 2                            ' False
    Debug.Print CDbl(CDate("1900/01/01")) - 2                                       ' 0
    Debug.Print CDbl(DateAdd("n", 30, "1899/12/31 23:30")) - 2                      ' -2.22044604925031E-16
End Sub

少しわかりづらいかもしれませんが、これは不具合でしょうか。
恐らくは Excel の 1900/3/1 以前の日付の扱いに関する互換性上の仕様が影響しているように
思いますが。
Excel 2016 です。
それ以外の Excel は検;できていません。

すべての返信 (15)

2020年10月17日土曜日 2:51 ✅回答済み

Excel2010 14.0.7258.5000
Excel2016 バージョン2009 (ビルド 13231.20390)
どちらも 本日時点での最新アップデート適用済みバージョンです。

結果は、全てのセルが 48:00 、小数表示でも 2.00・・・・・0 です。
Debug.Print の結果も下記の通り
 2
 2
True
True
True
 0
 0

2020年10月17日土曜日 3:05 ✅回答済み

> 恐らくは Excel の 1900/3/1 以前の日付の扱いに関する互換性上の仕様が影響しているように思いますが。
その問題は、シリアル値（数値）計算によって起こるものではなく、
単に**「日付表示に編集」する際の取り扱い（Excel vs VBA ）の違い**によるものです。
http://addinbox.sakura.ne.jp/Excel_Tips02.htm#Note2

2020年10月19日月曜日 3:26 ✅回答済み

> ="1900/2/28";1 と ="1900/3/1"-1 の結果が 1900/2/29 になります。
数式内に日付文字列があるので、加減計算の前にシリアル値変換【Excel用】が動きます。
その結果、上式は各々、=59;1 , =61-1 になり、その値[60]をExcelでの日付表現に
変換すれば 1900/2/29 です。

> Range("a1").Value = CDate("1900/2/28") ; 1 は 1900/3/1 になるけど、
VBAによるシリアル値変換なので、60;1 ⇒ 61 がセルに入り、
Excelでの日付表現に変換すれば 1900/3/1 です。

> Range("a1").Value = Range("a1").Value ; 1 は 1900/2/29 になってしまうので
先ずは、プラス[;]ではなく、マイナス[-]の書き間違いですよね。
上で、1900/3/1表示になっているセルの値は[61]、そこからマイナス１して上書きすれば
セルの値は[60]であり、Excelでの日付表現に変換すれば 1900/2/29 です。

> 計算時に誤差が出る可能性があります
1900年（明治33年）という【日付】データを扱うケースは「生年月日」もしくは「年表」位です。
生年月日であれば御年120歳です。その御歳の方のデータを扱う業務ですか？

それ以外で、その近辺の値（シリアル値６０以下）を扱うのは、勤務時間などの【時間データ】
としてです。「７２時間」を1900/1/3(Excel表示)や1900/1/2(VBA表示)という日付として
扱う事はありません。そもそも、８時間・２４時間などといった「時間データ」は日付ではありません。

ですから、Excel と VBA 間で日付表示がずれるという問題が実業務に影響を与えることはないです。

わたしの環境のExcel は 32bit版です。
64bit版のみで確実に再現する事象であれば、Microsoftへバグ報告してください。

このフォーラムに幾ら書き込んでもMicrosftは見ていませんから届きません。
Excel の『ファイル > フィードバック』で報告できます。
このスレッドのＵＲＬも添えて報告すると良いでしょう。

ちなみに、回答欄の右横にある「FeedBack」は、このフォーラムの運営に関する報告用（不適切な投稿の報告など）です。

2020年10月19日月曜日 12:12 ✅回答済み

> そもそもから ファイル - フィードバック がありません。
フィードバック Hub アプリというものを使って、報告する事も可能です。

フィードバック Hub アプリを使って Microsoft にフィードバックを送信する
https://support.microsoft.com/ja-jp/help/4021566

> Int(CDbl(DateAdd("n", 30, "1899/12/31 23:30"))) は 1 になってしまいます。
> なお、なぜか Int を CInt にすると 2 になります。
32bit版では Int でも 2 ですね。
32bit vs 64bit で桁数が増えた分、小数誤差の現れ方に違いがあるのかも？

> 24 時間以上の時間を扱いたかったので
24時間以上の時刻文字列の変換は標準ではできないので、
ユーザー定義関数を作るしかないですね。
下記は、私の kt関数アドイン/ktCTime の簡略版（時分のみ、エラーチェック省略）です。
http://addinbox.sakura.ne.jp/ktfunc_ref0303.htm

Function Str2Time(ByVal argTime As String) As Date
' argTime ... "hh:mm" , hh...1桁以上(48 等も可) , mm...0～59(1桁or2桁)
    Dim strHour As String
    Dim strMinute As String
    Dim intDays As Integer
    Dim intHour As Integer
    Dim intMinute As Integer

    strHour = Left(argTime, InStr(argTime, ":") - 1)
    strMinute = Right(argTime, Len(argTime) - Len(strHour) - 1)
    intDays = CInt(strHour) \ 24
    intHour = CInt(strHour) Mod 24
    intMinute = CInt(strMinute)
    Str2Time = CDate(intDays ; TimeSerial(intHour, intMinute, 0))
End Function

2020年10月20日火曜日 5:20 ✅回答済み

> 64bit になることで内部のシリアル値が
> より細かい値 (?) まで持てるように
> なっているのでしょうか。

Date型の実体は**元々 64bit浮動小数点実数(Double型)**です。

64bitシステム向けには、Long型(4Byte:32bit)の上位として
LongLong型(8Byte:64bit)が追加されていますが、日付は
上記の通り昔から 64bit データとして扱われていますので、
32/64bit Windows で変わりは無いと思いますけど、途中の
処理系のどこかで影響を受けている可能性も有り得ます。

ただ、真実は中の人にしか判りませんから、報告を上げて
中の人に気付いて貰うのを待つしかないですね。
（対応が有るのか無いのか自体も不明ですけどね）

2020年10月21日水曜日 20:08 ✅回答済み

Excel 2016 です。
それ以外の Excel は検;できていません。

手元の環境で試してみたところ、Excel 2016/32bit では再現せず、Excel 2019/64bit で事象を確認できました。
やはり 32bit / 64bit 版の違いに依存しているのは間違いなさそうですが、これが Excel の問題なのか、OLE API の問題なのかは分かりません。

また、当方ではこのような事象も確認できました。(Excel 2016/32bit および Excel 2019/64bit)

'「0」
Debug.Print (47.0# / 48.0#) ; (1.0# / 48.0#) - 1.0#

'「-3.81639164714898E-17」
Debug.Print CDbl(47.0# / 48.0#) ; CDbl(1.0# / 48.0#) - 1.0#

上記の値を Excel のセルに代入すると 24:00 (シリアル値 1) になってしまいます。

Date 型を渡すのは望ましくなさそうです。Excel のシリアル値は倍精度浮動小数点で扱われるものなので、Range.Value ではなく Range.Value2 で代入することを検討してみてください。(もしくは意図的に Double を用いる)

Rows(1).NumberFormatLocal = "[h]:mm:ss.000"
Range("B1").Value = CDate("1899/12/31 23:30")
Range("C1").Value = CDate("0:30")
Range("D1").Formula = "=B1;C1"
Range("E1").Value = CDate("1899/12/31 23:30") ; CDate("0:30")  'NG: Date 型を Value で渡すと、化けることがあるらしい
Range("F1").Value = CDbl(CDate("1899/12/31 23:30") ; CDate("0:30"))  'Double 値として渡せば 48:00 になった
Range("G1").Value = 1 * (CDate("1899/12/31 23:30") ; CDate("0:30"))  'Double 値として渡せば 48:00 になった
Range("H1").Value2 = CDate("1899/12/31 23:30") ; CDate("0:30")  'Value2 プロパティならシリアル値の読み書きとなり、48:00 になった
Range("B1:H1").EntireColumn.AutoFit

CDate("1899/12/31 23:30") ; CDate("0:30") は次のように
日付と時間に分離して計算し直すと正常に動作することを発見しました。

内部処理形式 Date の Variant 型は
#01/01/1900 00:00:00# を 2.0 として
#01/02/1900 00:00:00# を 3.0 として保持しますよね。

これは内部的には「有限桁数の 2 進小数」であり、ご存じの通り、64bit 長の浮動小数点数です。
浮動小数点数であるが故に、秒未満の精度での誤差を含む可能性があります。
VB.NET の Date 型であれば、内部的には 64bit 長の整数型(0.00001 ミリ秒精度)で管理されているので、こうした誤差は発生しないのですけれどね…。

12:00:00 であれば、半日すなわち 0.5 なので Double 型でも誤差なく保持できますが、
00:30:00 は要するに 1 ÷ 48 相当の値なので、0.0208333333…相当、
23:30:00 は要するに 47 ÷ 48 相当の値なので 0.9791666666…相当になるわけです。

これらの値は 10進小数でも 2進小数でも割り切れない値であり、循環小数となっています。
Double 型が保持できる桁数は有限ですから、循環小数は途中で打ち切られ、それが誤差となります。

「1.0# / 48.0#」で得られる Double 値の内部 2 進数表現を 10 進小数表記にしてみたところ、
0.02083333333333330095182844843293423764407634735107421875
になりました。同様に、「47.0# / 48.0#」な Double 値の場合には
0.97916666666666696272613990004174411296844482421875
でした。

しかし、Double 型(64bit)の有効桁数は、10進数換算で約 15.95458977 桁分しかないため、
実際にはそれ以降の桁の値は無価値と言えます。そのため VBA では、有効桁数 15 桁で打ち切って
これらを「2.08333333333333E-02」「0.979166666666667」として表示するようです。

Int(CDbl(CDate("1900/1/1"))) は 2 ですが
Int(CDbl(DateAdd("n", 30, "1899/12/31 23:30"))) は 1 に
なってしまいます。

1 になったのは、Double 値が「1.0 以上 2.0 未満の値」だったからですね。
3 時間や 6 時間という単位であれば誤差を生じませんが、1 時間や 30 分という単位だと、
Double 型では近;値となってしまうのが原因ですね。

「Debug.Print CDbl(47.0# / 48.0#) ; CDbl(1.0# / 48.0#)」を実行すると、
見た目上は 1 と表示されるため、誤差を含んでいることに気づきにくいですが、
「Debug.Print Int(CDbl(47.0# / 48.0#) ; CDbl(1.0# / 48.0#))」が
0 と表示されることから、ジャスト 1 ではなく、「1.0 未満の値」になっているのだと推察できます。

実際に「Debug.Print CDbl(47.0# / 48.0#) ; CDbl(1.0# / 48.0#) - 1.0#」とすると、
32bit 版 / 64bit版のいずれにおいても、0.0 にはならないことを確認できました。

そしてこの結果は「-3.81639164714898E-17」という値になりました。
Double 型の内部 2 進数から言えば、下記に相当する十分に小さい値ではありますが…厄介ですね。

Debug.Print -(2 ^ -55 ; 2 ^ -57 ; 2 ^ -58 ; 2 ^ -105 ; 2 ^ -106 ; 2 ^ -107)

なお、なぜか Int を CInt にすると 2 になります。

名前は;ていますが、Int と CInt は全く別の役割を担っています。

CInt は、値を Integer 型に変換(Convert)します。『データ型を変換するための関数』です。
小数値を渡した場合は、round to even な丸め処理 (いわゆる Banker's Rounding) となるため、CInt(2.5) は 2 になりますし、CInt(3.5) は 4 になります。

一方の Int 型は、『小数部を切り捨てて整数化する関数』ですが、こちらはデータ型の変換を伴いません。
Double 型を渡せば、Double 型のままですし、Date 型を渡せば Date 型のままです。
Currency や Decimal の場合も同様です。(Boolean 型だけは例外で、Integer になります)
Int 関数の切り捨ては、0 方向ではなく負の無限大方向に向いているため、Int(-3.0123#) は -3.0 ではなく、-4.0 になります。

2020年10月19日月曜日 1:13

Excel2010 14.0.7258.5000
Excel2016 バージョン2009 (ビルド 13231.20390)
どちらも 本日時点での最新アップデート適用済みバージョンです。

すみません、バージョンを書いていませんでした。
バージョンは 16.0.5056.1000 になっていました。
x64 です。

結果は、全てのセルが 48:00 、小数表示でも 2.00・・・・・0 です。
Debug.Print の結果も下記の通り
 2
 2
True
True
True
 0
 0

そうですか。
ほかの環境下でも同様の動作をしているようです。
もう少し調べてみます。

2020年10月19日月曜日 1:19

さっき、Windows Update をチェックしてみたら Offce 2016 用 (KB4484417) と
Excel 2016 用 (KB4486678) のアップデートがあったので適用中です。
適用後、再度試してみます。

2020年10月19日月曜日 1:33

すみません、バージョンを書いていませんでした。
バージョンは 16.0.5056.1000 になっていました。
x64 です。

別の環境の x64 (64bit) 版と x86 (32bit) 版でも試したところ、
64bit 版では再現し、32bit 版では再現しなかったので64bit 版固有の
問題の可能性があります。
(バージョン番号は同じ 16.0.5056.1000 でした)

2020年10月19日月曜日 2:11

> 恐らくは Excel の 1900/3/1 以前の日付の扱いに関する互換性上の仕様が影響しているように思いますが。
その問題は、シリアル値（数値）計算によって起こるものではなく、
単に**「日付表示に編集」する際の取り扱い（Excel vs VBA ）の違い**によるものです。
http://addinbox.sakura.ne.jp/Excel_Tips02.htm#Note2

正確にいうとシリアル値 60 が本来は 1900/2/28 でないといけないのに
Lotus が本来存在しない 1900/2/29 に割り当てていたせいでその互換性を
考慮して Excel も同様の仕様にしたためシリアル値 61 (1900/3/1) 未満が
1 日ずれるという問題ですね。
VBA の Date 型だと CDate(60) は 1900/2/28、Format(0, "yyyy/mm/dd") は
1899/12/30 と本来の正常な表示です。
ちなみに Excel だと、0 は 1900/1/0 というおかしな日付になってしまいます。
その結果、="1900/2/28";1 と ="1900/3/1"-1 の結果が 1900/2/29 になります。
Range("a1").Value = CDate("1900/2/28") ; 1 は 1900/3/1 になるけど、
Range("a1").Value = Range("a1").Value ; 1 は 1900/2/29 になってしまうので
計算時に誤差が出る可能性があります。
このあたりのずれが関係しているのかなと思ったのですが、ビットの違いによる
問題であるならどうやら関係なさそうですね。

2020年10月19日月曜日 4:50

> ="1900/2/28";1 と ="1900/3/1"-1 の結果が 1900/2/29 になります。
数式内に日付文字列があるので、加減計算の前にシリアル値変換【Excel用】が動きます。
その結果、上式は各々、=59;1 , =61-1 になり、その値[60]をExcelでの日付表現に
変換すれば 1900/2/29 です。

> Range("a1").Value = CDate("1900/2/28") ; 1 は 1900/3/1 になるけど、
VBAによるシリアル値変換なので、60;1 ⇒ 61 がセルに入り、
Excelでの日付表現に変換すれば 1900/3/1 です。

これは完全な間違いでした。
Range("a1").Value = CDate("1900/2/28") は Excel 上で
1900/2/29 になるからこれで正しいですね。

> Range("a1").Value = Range("a1").Value ; 1 は 1900/2/29 になってしまうので
先ずは、プラス[;]ではなく、マイナス[-]の書き間違いですよね。
上で、1900/3/1表示になっているセルの値は[61]、そこからマイナス１して上書きすれば
セルの値は[60]であり、Excelでの日付表現に変換すれば 1900/2/29 です。

わかりづらくてすみません。

Range("a1").Value = CDate("1900/2/28")
Range("a1").Value = Range("a1").Value ; 1

の意味で書いたのですがそもそもから先述の内容自体が間違いだったので
これも間違っていますね。
(CDate("1900/2/28") ではなく、CDate("1900/2/27") が正しいので)

> 計算時に誤差が出る可能性があります
1900年（明治33年）という【日付】データを扱うケースは「生年月日」もしくは「年表」位です。
生年月日であれば御年120歳です。その御歳の方のデータを扱う業務ですか？

それ以外で、その近辺の値（シリアル値６０以下）を扱うのは、勤務時間などの【時間データ】
としてです。「７２時間」を1900/1/3(Excel表示)や1900/1/2(VBA表示)という日付として
扱う事はありません。そもそも、８時間・２４時間などといった「時間データ」は日付ではありません。

ですから、Excel と VBA 間で日付表示がずれるという問題が実業務に影響を与えることはないです。

すみません。
実際に実際にこのあたりの年代を日付として処理する例はめったにないとは
思いますが、Excel の機能上は表現できる形式なので。

わたしの環境のExcel は 32bit版です。
64bit版のみで確実に再現する事象であれば、Microsoftへバグ報告してください。

報告するにしてもまずは不具合なのか仕様なのかを確定させたかっただけです。
不具合なら報告したほうがいいし、仕様ならなぜそのような動作になるのか
知りたかったのですが、32bit 版では再現しないことから何らかの不具合である
可能性が高まりました。
最近のバージョンの問題でないようなら昔からあった問題である可能性も
高そうですが、今まで報告がなかったのは再現手順がかなり限定されていたから
でしょうかね。
さらにちなみに Int(CDbl(CDate("1900/1/1"))) は 2 ですが
Int(CDbl(DateAdd("n", 30, "1899/12/31 23:30"))) は 1 に
なってしまいます。
なお、なぜか Int を CInt にすると 2 になります。
あと、日付形式での処理をしたのは CDate("24:00") などがエラーになって
しまうけど、24 時間以上の時間を扱いたかったので、上記のような書き方を
していました。

2020年10月19日月曜日 6:12

64bit版のみで確実に再現する事象であれば、Microsoftへバグ報告してください。

このフォーラムに幾ら書き込んでもMicrosftは見ていませんから届きません。
Excel の『ファイル > フィードバック』で報告できます。
このスレッドのＵＲＬも添えて報告すると良いでしょう。

不具合報告しようと思ったのですが、そもそもから ファイル - フィードバック がありません。
可能性があるのは 情報 か アカウント だと思いますがどちらにもありません。

2020年10月19日月曜日 18:48

自宅の Excel 2019 (バージョン 2009 ビルド 16.0.13231.20390 x64) でも
再現を確認しました。
やはり、64bit 版であることが再現の条件のようです。
あと、自宅の方の Excel にはフィードバックがあったので、バージョンの
問題でしょうかね。
プリインストール版なので 1 年間 (?) の Microsft 365 のライセンス付きのはず。

2020年10月19日月曜日 19:06

CDate("1899/12/31 23:30") ; CDate("0:30") は次のように
日付と時間に分離して計算し直すと正常に動作することを発見しました。

DateValue(CDate("1899/12/31 23:30")) ; (TimeValue(CDate("1899/12/31 23:30")) ; CDate("0:30"))

足す時間も 24 時間を超えている場合は同じように日付と時間に分離しないと
いけなそうですが。
恐らく、表示できないほど小さな誤差の丸めが原因っぽいです。
64bit になることで内部のシリアル値がより細かい値 (?) まで持てるように
なっているのでしょうか。

2020年10月26日月曜日 2:34

返信ありがとうございます。
しばらく見ていなかったら詳細な回答をくださったようで助かります。
こういった丸めによる誤差は仕方ないところなんでしょうけど、たびたび
厄介な不具合の原因にもなるので、難しいところですね。
特に厄介なのが 32bit だと再現せず、64bit のみ再現するところでしょうか。
これからは少しずつ 64bit が主流になってくると思いますし、32bit では
再現しないのなら修正してほしいなと思います。
一応、MS にはこの問題はこの投稿の URL を記載の上、フェードバックを
送っておきました。

計算結果を代入する際に Value2 を使う方法でうまくいっているようです。

なお、以下のように Excel のセルを経由すると Value2 を用いても
代入せずに VBA 上で処理する場合は不具合が出ます。
VBA 上で直接、計算したときは再現しないんですけどね。
Excel 上で表示するために代入するなら Value2 が使えそうですが、
Excel の時間を計算して VBA 上で扱うときは無理なようですね。
(1 日戻ってしまう)

Range("a1").Value2 = CDate("23:30") ; 1 ' 47:30
Debug.Print Application.WorksheetFunction.Text(Range("a1").Value2 ; CDate("0:30"), "[h]:mm") ' 本来 48:00 になるはずが 24:00 になる

' 以下なら不具合が出ない
Debug.Print Application.WorksheetFunction.Text(1 ; CDate("23:30") ; CDate("0:30"), "[h]:mm") ' 48:00