Excel VBA转换毫秒级Unix时间戳为DateTime时遇溢出错误求助

Root Cause of Overflow #

Your DateAdd call fails because you’re passing the full millisecond timestamp as the number of seconds. 1637402076084 milliseconds equals ~1.6e12 seconds—way beyond the range of dates VBA can handle (max date is 9999-12-31, which is ~3.15e11 seconds since the Unix epoch). Even if you corrected this by dividing by 1000, using DateAdd in a loop for large datasets is slower than direct date arithmetic.

Efficient Array-Based Solution #

Replicate the Excel formula logic in VBA, which works because Excel stores dates as days since 1900/01/01 (or 1904 for Mac). For Unix timestamps (milliseconds since 1970/01/01), the conversion is:
Excel Date = (Unix Timestamp / 86400000) + DATE(1970,1,1)

This approach is ideal for array processing because it minimizes worksheet interactions (the biggest VBA performance bottleneck for large datasets).

Example Code #

Sub ConvertUnixTimestamps()
    Dim ws As Worksheet
    Dim inputRange As Range
    Dim inputArray As Variant
    Dim outputArray As Variant
    Dim i As Long, j As Long
    
    ' Configure your worksheet and input range
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set inputRange = ws.Range("C2:C100000") ' Replace with your dataset range
    
    ' Load data into array (far faster than cell-by-cell access)
    inputArray = inputRange.Value
    
    ' Initialize output array to match input dimensions
    ReDim outputArray(1 To UBound(inputArray, 1), 1 To UBound(inputArray, 2))
    
    ' Convert timestamps using direct arithmetic
    For i = 1 To UBound(inputArray, 1)
        For j = 1 To UBound(inputArray, 2)
            If IsNumeric(inputArray(i, j)) Then
                ' Same logic as Excel formula: (timestamp / ms per day) + epoch date
                outputArray(i, j) = inputArray(i, j) / 86400000 + DateSerial(1970, 1, 1)
                ' Optional: Preserve millisecond display (format cell later with "yyyy-mm-dd hh:mm:ss.000")
            Else
                outputArray(i, j) = inputArray(i, j) ' Keep non-numeric values unchanged
            End If
        Next j
    Next i
    
    ' Write converted data back to worksheet (adjust target column as needed)
    inputRange.Offset(0, 1).Value = outputArray ' Writes to column D
End Sub

Key Notes #

• Performance: Loading data into an array first cuts down on slow worksheet read/write operations, critical for large datasets.
• Millisecond Precision: VBA’s Date type is a Double (integer part = days, fractional part = time), so the conversion retains millisecond data. To display milliseconds, apply a custom cell format like yyyy-mm-dd hh:mm:ss.000.
• Single Value Conversion: For individual timestamps, use this concise version:

  Dim unixMs As Double
  Dim excelDate As Date
  unixMs = 1637402076084#
  excelDate = unixMs / 86400000 + DateSerial(1970, 1, 1)
  ' Get formatted string with milliseconds:
  Dim formattedDate As String
  formattedDate = Format(excelDate, "yyyy-mm-dd hh:mm:ss") & "." & Format((excelDate - Int(excelDate)) * 86400000, "000")
  

内容的提问来源于stack exchange，提问作者Michael Liew