excel - 将 VBA 中的“= Now”转换为 EST

Question

您好，我目前正在使用在伦敦和纽约都使用的宏。宏中有一个部分使用=now它为操作添加时间戳，这创造了一种独特的情况，我们在纽约执行的操作看起来像是在伦敦执行的操作之前发生的。

我的问题是您能否转换=now为 EST，以便在整个项目中创建统一的时间戳？

作为参考，实际代码行如下所示

mws.Range(Cells(Lastmwsr + 1, 2), Cells(Lastmwsr + 1, 2)).Value = Now

编辑：包括将 +5 添加到小时的解决方案仍然需要定义如何识别时区以调用参数，因为这必须在多个区域中使用。

谢谢，

标签： exceltimevba

Answer 1

我通读了 Chip Pearson 的页面，承认它在我的代码中实现它的技能水平有点高。

http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx

我认为后期 Chips 代码中有一个错误 - 他ConvertLocalToGMT并GetLocalTimeFromGMT使用了一个名为的变量StartTime- 我认为在这两种情况下，这应该是传递给函数的变量。

要使用他的代码，您可以编写如下内容：

Range("A1") = GetLocalTimeFromGMT(Now())  

或者

Range("A1") = ConvertLocalToGMT(Now())

我在下面包含了他网站上的代码。只需复制并粘贴到新模块中即可。

Option Explicit

    Private Type SYSTEMTIME
        wYear As Integer
        wMonth As Integer
        wDayOfWeek As Integer
        wDay As Integer
        wHour As Integer
        wMinute As Integer
        wSecond As Integer
        wMilliseconds As Integer
    End Type

    Private Type TIME_ZONE_INFORMATION
        Bias As Long
        StandardName(0 To 31) As Integer
        StandardDate As SYSTEMTIME
        StandardBias As Long
        DaylightName(0 To 31) As Integer
        DaylightDate As SYSTEMTIME
        DaylightBias As Long
    End Type

    Private Enum TIME_ZONE
        TIME_ZONE_ID_INVALID = 0        ' Cannot determine DST
        TIME_ZONE_STANDARD = 1          ' Standard Time, not Daylight
        TIME_ZONE_DAYLIGHT = 2          ' Daylight Time, not Standard
    End Enum

    Private Declare Function GetTimeZoneInformation Lib "kernel32" _
        (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

    Private Declare Sub GetSystemTime Lib "kernel32" _
        (lpSystemTime As SYSTEMTIME)


    Function ConvertLocalToGMT(Optional LocalTime As Date) As Date
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' ConvertLocalToGMT
    ' This function returns the GMT based on LocalTime, if provided.
    ' If LocalTime is not equal to 0, the GMT corresponding to LocalTime
    ' is returned. If LocalTime is 0, the GMT corresponding to the local
    ' time is returned. Since GMT isn't affected by DST, we need to
    ' subtract 1 hour if we are presently in GMT.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim T As Date
        Dim TZI As TIME_ZONE_INFORMATION
        Dim DST As TIME_ZONE
        Dim GMT As Date

        If LocalTime <= 0 Then
            T = Now
        Else
            T = LocalTime
        End If
        DST = GetTimeZoneInformation(TZI)
        GMT = T + TimeSerial(0, TZI.Bias, 0) - IIf(DST = TIME_ZONE_DAYLIGHT, TimeSerial(1, 0, 0), 0)
        ConvertLocalToGMT = GMT

    End Function

    Function GetLocalTimeFromGMT(Optional GMTTime As Date) As Date
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' GetLocalTimeFromGMT
    ' This returns the Local Time from a GMT time. If GMTTime is present and
    ' greater than 0, it is assumed to be the GMT from which we will calculate
    ' Local Time. If GMTTime is 0 or omitted, it is assumed to be the GMT
    ' time.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim GMT As Date
        Dim TZI As TIME_ZONE_INFORMATION
        Dim DST As TIME_ZONE
        Dim LocalTime As Date

        If GMTTime <= 0 Then
            GMT = Now
        Else
            GMT = GMTTime
        End If
        DST = GetTimeZoneInformation(TZI)
        LocalTime = GMT - TimeSerial(0, TZI.Bias, 0) + IIf(DST = TIME_ZONE_DAYLIGHT, TimeSerial(1, 0, 0), 0)
        GetLocalTimeFromGMT = LocalTime

    End Function