Excelの時刻セルはDate型として扱い24:00を壊さず送る

概要

Excel VBAで時刻セルを読み取り、外部システムへ9:00のような文字列で渡すときは注意が必要です。 セルの見た目は時刻でも、.Valueで読むとDate型のVariantとして返ることがあります。

IsNumericだけで判定すると、24:00が1899/12/31のような日付文字列へ化けることがあります。

なぜ壊れるか

時刻セルを.Valueで読むと、VBA側ではDate型として扱われます。 そしてIsNumeric(Date値)はFalseです。

24:00は、Excel内部では1日分のシリアル値です。 そのため、Dateとして文字列化すると「時刻」ではなく「日付」として出てしまいます。

使用例

Dim textValue As String
textValue = FmtTime(Range("A1").Value)

コピー可能な実装コード

Private Function FmtTime(ByVal value As Variant) As String
    Dim serial As Double
    Dim fraction As Double

    If IsEmpty(value) Then Exit Function
    If Len(Trim$(CStr(value))) = 0 Then Exit Function

    If IsDate(value) Or IsNumeric(value) Then
        serial = CDbl(CDate(value))
    Else
        FmtTime = Trim$(CStr(value))
        Exit Function
    End If

    fraction = serial - Int(serial)

    If Abs(serial - 1#) < 1E-7 Then
        FmtTime = "24:00"
    Else
        FmtTime = Format$(fraction, "h:mm")
    End If
End Function

Private Function FmtDate(ByVal value As Variant) As String
    If IsDate(value) Then
        FmtDate = Format$(value, "yyyy/mm/dd")
    Else
        FmtDate = Trim$(CStr(value))
    End If
End Function

初心者向けコード解説

IsDate(value) Or IsNumeric(value)としているのは、時刻セルがDate型で返る場合と、数値として返る場合の両方を受けるためです。

serial = CDbl(CDate(value))

この1行で、Date型でも数値でもExcelの日数シリアルへそろえます。 そのうえで、小数部だけを取り出すと時刻部分になります。

fraction = serial - Int(serial)

24:00は内部値がちょうど1.0なので、通常の小数部処理では0:00になってしまいます。 そのため、1.0に近い場合だけ24:00として扱います。

日付はスラッシュ形式で渡す

外部のJavaScriptやGASへ日付を渡す場合、yyyy-mm-ddではなくyyyy/mm/ddにする方が安全です。 ハイフン形式はUTC日付として解釈され、タイムゾーンによって日付がずれることがあります。

関連記事

• 勤務時間の通常・深夜按分は区間の重なりで計算する
• Excel VBAからGAS Web APIへTSVでPOSTする実装定石
• SheetJSでExcel出力するときの日付・時間セルの扱い

まとめ

Excelの時刻セルは、見た目ではなく内部値とVBA側の型を前提に扱います。 IsNumericだけに頼らず、IsDateも見て日数シリアルへそろえることで、24:00を壊さず外部へ渡せます。