How to convert from indian time zone to US time zone

[prabhuduraraj09]

Hi all,

I need some serious help here, not sure if this is possible. I have below Time stamp code.

which inputs current system time, All the system time will be in Time zone of (UTC-05:00) Eastern Time (US & Canada)

i am fine with system inputting this time.


The challenge is, some of the system will be in Time zone of (UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi.
Here i need help of Vba code to convert actual Timzone time to (UTC-05:00) Eastern Time (US & Canada) and according to my code
the converted time will be stamped in active cell.


Time zone (UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi should be converted to (UTC-05:00) Eastern Time (US & Canada).

my code is as follows,

Sub Timestamp()

ActiveCell.Value = VBA.Time


End Sub

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[prabhuduraraj09]

Hi ,

I have attached sample file.

My system time zone is in (UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi
when i ran my code, it published the current time as 04:59:40 Pm.


I need macro to publish the time in following time zone (UTC-05:00) Eastern Time (US & Canada) always.


If my system time zone is already in Us & Canada time zone, then macro can publish the current time as it is.
If my System time zone is in "(UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi" then macro should convert the system time
to "(UTC-05:00) Eastern Time (US & Canada)" and publish the revised time.


So core point is macro should publish only in "(UTC-05:00) Eastern Time (US & Canada)" time zone, current time.

hope i have given details.

[Richard Buttrey]

If there is 11.5 hours difference between Indian and USA time then an Indian time 4:59:40 PM should convert to a US time of 5:29:40 AM.
So why do you say it should be 7:30:04 AM?

[Logit]

.
Could it be because India does not use Daylight Savings Time and the US does ?

[Richard Buttrey]

.
Could it be because India does not use Daylight Savings Time and the US does ?

Yes it's a possibility and the time difference is 9.5 hours not 10.5. But in practice unless there's something in the data to tell the formula to adjust for daylight saving, unless of course that can be deduced from the date, then I don't see how it can be worked out.

[protonLeah]

Sub Timestamp()
    Dim TimeDif         As Double, _
        DaylightStart   As Date, _
        DaylightEnd     As Date, _
        ThisYear        As Long, _
        IsDaylight      As Boolean
        
    Const March     As Long = 3
    Const November  As Long = 11
    
'   Generically you can get the nth xday of the month with this formula
'   =DATE(B2,A2,1+7*n)-WEEKDAY(DATE(B2,A2,8-xday))
'   daylight start = second (n=2) Sunday (xday =1) in March @ 02:00:00
    
    Const Sunday    As Long = 1
    Const nth       As Long = 2
    Const xday      As Long = 1 'sunday = 1st day of week
    Const TwoAM     As Double = 2 / 24
    
    ThisYear = Year(Now())
    
'begins at 2:00 a.m. on the second Sunday of March and
    
    DaylightStart = DateValue(March & "/" & 1 + 7 * nth & "/" & ThisYear) - Weekday(DateValue(March & " / " & 8 - xday & " / " & ThisYear))
    DaylightStart = DateValue(DaylightStart) + TwoAM
    
'ends at 2:00 a.m. on the first Sunday of November
    DaylightEnd = DateValue(November & "/" & 1 + 7 * 1 & "/" & ThisYear)
    DaylightEnd = DaylightEnd + TwoAM
    
'is daylight in effect?
    IsDaylight = Now >= DaylightStart And Now <= DaylightEnd
    TimeDif = IIf(IsDaylight, 9.5, 10.5) / 24
    
    TimeStampValue = TimeValue(Now() - TimeDif)
End Sub

[prabhuduraraj09]

Hi Ben and all,

I appreciate each one of your contribution on this.

i have tested the code, it does converts Indian time to US time.

However, if my system time zone is already in US time zone then i dont want any changes in publishing time.
So if my time zone is in Indian standard time then this conversion should happen.

is this possible ?

[Richard Buttrey]

The following code is from Chip Pearson. Put it in a standard module and run the AAA procedure. You can then use the StandardName variable value in an IF test to determine whether you need to apply the conversion or not.

 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
    
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' NOTE: If you are using the Windows WinAPI Viewer Add-In to get
    ' function declarations, not that there is an error in the
    ' TIME_ZONE_INFORMATION structure. It defines StandardName and
    ' DaylightName As 32. This is fine if you have an Option Base
    ' directive to set the lower bound of arrays to 1. However, if
    ' your Option Base directive is set to 0 or you have no
    ' Option Base diretive, the code won't work. Instead,
    ' change the (32) to (0 To 31).
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    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
    
    
    ''''''''''''''''''''''''''''''''''''''''''''''
    ' These give symbolic names to the time zone
    ' values returned by GetTimeZoneInformation .
    ''''''''''''''''''''''''''''''''''''''''''''''
    
    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 IntArrayToString(V As Variant) As String

        Dim N As Long
        Dim S As String
        For N = LBound(V) To UBound(V)
            S = S & Chr(V(N))
        Next N
        IntArrayToString = S

    End Function

    Sub AAA()

        Dim TZI As TIME_ZONE_INFORMATION
        Dim DST As TIME_ZONE
        Dim StandardName As String
        
        DST = GetTimeZoneInformation(TZI)
        StandardName = IntArrayToString(TZI.StandardName)
        Debug.Print StandardName

    End Sub

[AliGW]

The late, great Chip Pearson - sadly missed on all forums he frequented.