英語形式の日付文字列を日付型に変換

' 機能概要: 英語形式の日付文字列を日付型に変換
' 引数:
'   strDateText - 変換する日付の文字列（例: "Wednesday, December 13, 2023 9:00 AM"）
' 戻り値:
'   Variant - 変換された日付。変換できない場合はNullを返す。

Function ConvertEnglishDateString(strDateText As String) As Variant
    Dim arrMonths As Variant
    arrMonths = Array("January", "February", "March", "April", "May", "June", _
                      "July", "August", "September", "October", "November", "December")
    Dim i As Integer
    Dim monthNumber As Integer
    Dim day As Integer
    Dim year As Integer
    Dim hour As Integer
    Dim minute As Integer
    Dim arrDateTime As Variant
    Dim strTimePart As String
    Dim dt As Variant

    ' 曜日名を削除
    strDateText = Mid(strDateText, InStr(strDateText, ",") + 2)

    ' 日付と時間を分割
    arrDateTime = Split(strDateText, " ")
    If UBound(arrDateTime) < 4 Then
        ConvertEnglishDateString = Null
        Exit Function
    End If

    ' 月を数値に変換
    For i = LBound(arrMonths) To UBound(arrMonths)
        If arrMonths(i) = arrDateTime(0) Then
            monthNumber = i + 1
            Exit For
        End If
    Next i

    ' 日と年を取得
    day = Val(arrDateTime(1))
    year = Val(arrDateTime(2))

    ' 時間を取得（AM/PMの処理を含む）
    strTimePart = arrDateTime(3) & " " & arrDateTime(4)
    dt = CDate(strTimePart)
    hour = Hour(dt)
    minute = Minute(dt)

    ' DateSerialとTimeSerialを使用して日付と時間を結合
    ConvertEnglishDateString = DateSerial(year, monthNumber, day) + TimeSerial(hour, minute, 0)
End Function