CODEWITHSUNDEEP
exceltimevba
dom176
dom176

Reputation: 187

Converting "= Now" in VBA to EST

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.

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

EDIT: 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.

Thanks,

Upvotes: 0

Views: 4662

Answers (3)

Bernhard Laven
Bernhard Laven

Reputation: 151

I had to find a similar solution for a workflow system where the long answer from Get Timezone Information in VBA (Excel) could not be used (too long code).

I also had to format the date, so I combined the functions Format and DateAdd:

Format(DateAdd("h", 5, Now), "dd.MM.yyyy HH:mm:ss")

Some explanation

  • Now returns the current system date and time.
  • "h" specifies that the hours from the returned date and time (Now) have to be calculated up by "5".
  • "dd.MM.yyyy HH:mm:ss" specifies the syntax output.

For example the output of the date and time: 28.07.2020 00:42:09, would be 28.07.2020 05:42:09.

This solution considers leap years as well.

I know this is a dirty solution and not the perfect answer for the question, but maybe it helps somebody anyways.

Further documentary:

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

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

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())

or 

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

Upvotes: 2

Vityata
Vityata

Reputation: 43575

This is a way to display the value of Now adding 5 hours to it. You may consider a function as well.

Public Sub TestMe()

    Dim fixTime As Long
    fixTime = 5
    Range("a1") = TimeSerial(Hour(Now) + fixTime, Minute(Now), Second(Now))

End Sub

Upvotes: 0

Related Questions