时间戳

首页 > 解决方案 > Excel VBA将日期与时间转换

excel - Excel VBA将日期与时间转换

问题描述

我有 2 个带有时间数据的单元格，其格式如下：

"A1" = Sep 01 2018 00:01:33.707 
"A2" = Sep 01 2018 00:01:49.917

我需要在 excel VBA 中创建一个按钮和方法，如果时间“A2”比“A1”多 90 秒，则将“A3”单元格设置为 true。这是我到目前为止所拥有的，但它不起作用：

Sub Macro2()
    Dim str1 As String, str2 As String

    With Worksheets("sheet5")

        str1 = .Cells(1, "A").Text
        str2 = .Cells(2, "A").Text

        'greater than 90m seconds in A3
        .Cells(3, "A") = CBool(Abs((DateValue(Left(str1, 6) & "," & Mid(str1, 7, 5)) + _
                                    TimeValue(Mid(str1, 13, 8)) + TimeSerial(0, 0, 1) * CDbl(Right(str1, 4))) - _
                                   (DateValue(Left(str2, 6) & "," & Mid(str2, 7, 5)) + _
                                   TimeValue(Mid(str2, 13, 8)) + TimeSerial(0, 0, 1) * CDbl(Right(str2, 4)))) > _
                                   TimeSerial(0, 0, 90))
        'actual absolute difference in A4
        .Cells(4, "A") = Abs((DateValue(Left(str1, 6) & "," & Mid(str1, 7, 5)) + _
                                    TimeValue(Mid(str1, 13, 8)) + TimeSerial(0, 0, 1) * CDbl(Right(str1, 4))) - _
                                   (DateValue(Left(str2, 6) & "," & Mid(str2, 7, 5)) + _
                                    TimeValue(Mid(str2, 13, 8)) + TimeSerial(0, 0, 1) * CDbl(Right(str2, 4))))
    End With End Sub

上面给出了错误，因为日期函数适用于系统语言环境，在我的例子中是希伯来语，而数据是英语。

另一种可能有帮助的方法是将所有列“A”（包含日期）转换为可与 VBA 上的日期和时间函数一起使用的系统本地日期（不知道如何执行此操作）。

请帮忙

标签： excelvba

解决方案

我已将您的任务分为 3 个功能。

a) 辅助函数将月份的 3 个字符转换为整数。它看起来有点笨拙，可能还有其他方法，但使用 large 的优点Select Case是，如果出现不同语言的月份名称，它很容易理解和适应：

Function getMonthFromName(monthName As String) As Integer

    Select Case UCase(monthName)
        Case "JAN": getMonthFromName = 1
        Case "FEB": getMonthFromName = 2
        Case "MAR": getMonthFromName = 3
        Case "APR": getMonthFromName = 4
        (...)
        Case "SEP": getMonthFromName = 9
        (...)
    End Select

End Function

b) 将字符串转换为日期的函数。它采用您提供的形式的日期格式，但如果格式发生变化，它很容易适应（为简单起见，秒是四舍五入的）

Function GetDateFromString(dt As String) As Date

    Dim tokens() As String
    tokens = Split(Replace(dt, ":", " "), " ")

    Dim day As Integer, month As Integer, year As Integer
    month = getMonthFromName(CStr(tokens(0)))
    day = Val(tokens(1))
    year = Val(tokens(2))

    Dim hour As Integer, minute As Integer, second As Double
    hour = Val(tokens(3))
    minute = Val(tokens(4))
    second = Round(Val(tokens(5)), 0)

    GetDateFromString = DateSerial(year, month, day) + TimeSerial(hour, minute, second)
End Function

c) 以秒为单位计算 2 个日期之差的函数。VBA（和许多其他环境）中的日期存储为Double，其中日期部分是整数部分，日期是余数。这使得使用日期值进行计算变得容易。

Function DateDiffInSeconds(d1 As String, d2 As String) As Long
    Dim diff As Double
    diff = GetDateFromString(d2) - GetDateFromString(d1)
    DateDiffInSeconds = diff * 24 * 60 * 60
End Function

更新以处理毫秒：更改GetDateFromString-function。在这种情况下，DateDiffInSeconds应该返回 adouble而不是 a long。

Function GetDateFromString(dt As String) As Date

    Const MillSecPerHour As Long = 24& * 60 * 60 * 1000

    Dim tokens() As String
    tokens = Split(Replace(Replace(dt, ".", " "), ":", " "), " ")

    Dim day As Integer, month As Integer, year As Integer
    month = getMonthFromName(CStr(tokens(0)))
    day = Val(tokens(1))
    year = Val(tokens(2))

    Dim hour As Integer, minute As Integer, second As Integer, milli As Integer
    hour = Val(tokens(3))
    minute = Val(tokens(4))
    second = Val(tokens(5))
    milli = Val(tokens(6))

    GetDateFromString = DateSerial(year, month, day) _
                      + TimeSerial(hour, minute, second) _
                      + milli / MillSecPerHour
End Function

推荐阅读