24時間以上を表す文字列をDate型に変換する

24時間以上を表す文字列とは、
"28:00"とか"32:12"などの文字列のことです。

これをDate型に変換するために
CDate関数を使って

CDate("28:00")

とするとエラーになります。
(実行時エラー'13':型が一致しません。)

念のため、
IsDate関数でDate型に変換できるかどうか調べてみる。

CDate関数を使う前に
IsDate関数でDate型に変換できるかどうか調べると
型が一致しなかった場合のエラーを防げます。

IsDate("28:00") = False

ちなみに

IsDate("00:00") = True
IsDate("23:59") = True
IsDate("23:59:59") = True
IsDate("24:00:00") = False

やはり24時間以上だと
CDate関数でDate型に変換できないようです。

ところが、
Excelのシートのセルに

28:00

と入力すると
セルの書式設定の表示形式が自動的に

[h]:mm:ss

になり、セルには

28:00:00

と表示され24時間以上で表示されます。
値も日付型になっています。

28:00:00
= 1900/1/1 4:00
= 1.16666666666667

Excel自体には24時間以上を表す文字列を
Date型に変換する機能があるにも関わらず、
VBAでそれを利用する方法が見つからなかったので
まわりくどい方法を１つ。

基本的な流れは、
24時間以上を表す文字列を
あるセルに入力して
Excelが自動で日付に変換した値を
取り出して使います。

まず、
CDateで変換できるものはCDateで変換します。

CDateで変換できない文字列を

24時間以上の文字列(例："28:00")
その他の文字列(例："abc")

この2つと想定します。

この2つの区別に
セルの書式設定の表示形式が
自動的に　[h]:mm:ss　になることを利用します。

コードはこちら

Function CDate2(Str As String) As Variant
   
    'CDate関数で変換できるものはCDateで
    If IsDate(Str) Then
        CDate2 = CDate(Str)
        Exit Function
    End If
   
    Dim MyCell As Range
    Dim sh As Object
   
    Application.ScreenUpdating = False
   
    'Sheets("temp")がなければシートの追加
    For Each sh In Worksheets
        If sh.Name = "temp" Then
            GoTo NextStep
        End If
    Next sh
    Sheets.Add.Name = "temp"
   
NextStep:
    Set MyCell = Sheets("temp").Range("A1")
    MyCell.Clear
   
    MyCell = Str
   
    If MyCell.NumberFormat = "[h]:mm:ss" Then
        CDate2 = CDate(MyCell)
    End If

    Application.ScreenUpdating = True
End Function

Sub macro100918a()
'CDate2関数使用例

    Debug.Print CDate2("12:32")
    Debug.Print CDate2("28:00")
    Debug.Print CDate2("abc")
    Debug.Print IsEmpty(CDate2("abc"))
   
End Sub

このCDate2関数を使った結果の例：

CDate2("12:32") = "12:32:00"
CDate2("28:00") = "1899/12/31 4:00:00"
CDate2("abc") = ""

「その他の文字列」を入れたとき
CDate2関数が返す値は空白ですが、

CDate2("abc") = CDate2("0:00:00")

となり
"abc"と"0:00:00"の区別がつきません。

そこでCDate2関数をVariant型にして、
値が初期化されていないか調べる
IsEmpty関数を使えるようにしました。

IsEmpty(CDate2("12:32")) = False
IsEmpty(CDate2("28:00")) = False
IsEmpty(CDate2("abc")) = True
IsEmpty(CDate2("0:00:00")) = False

まあ
こんなややこしいことをしなくても、
24時間以上を表す文字列をDate型に変換する
関数なりメソッドなりを見つける日が
いつか来ると思います。

いままでも
しばしば、まわりくどいことをしてます。
当サイトを参考にするときはお気をつけて。