转换"= 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屋!