转换"= Now"在VBA到EST [英] Converting "= Now" in VBA to EST

问题描述

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

Hello I am currently using a macro that is leveraged in both London and NY. There is a section in the macro that timestamps the action using =now this is creating a unique situation where actions we perform in NY look like they occurred before actions performed in London.

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

My question is are you able to convert =now to EST, as to create a uniform timestamp throughout the project?

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

for reference the line of actual code looks like this

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

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

Solutions that include adding +5 to the hour, still need to define how to recognize the time zone to call the argument as this has to be used in multiple regions.

谢谢

推荐答案

我通读了Chip Pearson的页面，诚然，它比我的页面略高技能水平将其实现到我的代码中.

I read through Chip Pearson's page, admittedly its a bit above my skill level implementing that into my code.

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

我认为最新的Chips代码中有错误-他的 ConvertLocalToGMT 和 GetLocalTimeFromGMT 使用了名为 StartTime 的变量-我认为在两种情况下这应该是传递给函数的变量.

I think there's an error in the late Chips code - his ConvertLocalToGMT and GetLocalTimeFromGMT uses a variable called StartTime - I think in both cases this should be the variable that is being passed to the function.

要使用他的代码，您需要编写以下内容:

To use his code you'd write something like:

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

或

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

我已经在下面的站点中包含了代码.只需复制并粘贴到新模块中即可.

I've included the code from his site below. Just copy and paste into a new module.

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

这篇关于转换"= Now"在VBA到EST的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！