Excel Macro Local GMT Display


New Member
Aug 28, 2010
I am part the way through an assignment. I have been able to create macro to give me now time (thanks to post by VoG in Feb 07) but also need to display current GMT to finish building global time converter. This is the closest I have found (below - from http://msdn.microsoft.com/en-us/library/system.timezone.aspx) but no real VBA experience and don't know what I am looking at. Can anyone explain/assist with this? I also have a second question below this insert.
' Example of selected TimeZone class elements.
Imports System
Imports System.Globalization
Imports Microsoft.VisualBasic

Module TimeZoneDemo

Sub Main( )

Const dataFmt As String = "{0,-30}{1}"
Const timeFmt As String = "{0,-30}{1:yyyy-MM-dd HH:mm}"

Console.WriteLine( "This example of selected " & _
"TimeZone class elements generates the following " & _
vbCrLf & "output, which varies depending on the " & _
"time zone in which it is run." & vbCrLf )

' Get the local time zone and the current local time and year.
Dim localZone As TimeZone = TimeZone.CurrentTimeZone
Dim currentDate As DateTime = DateTime.Now
Dim currentYear As Integer = currentDate.Year

' Display the names for standard time and daylight saving
' time for the local time zone.
Console.WriteLine( dataFmt, "Standard time name:", _
localZone.StandardName )
Console.WriteLine( dataFmt, "Daylight saving time name:", _
localZone.DaylightName )

' Display the current date and time and show if they occur
' in daylight saving time.
Console.WriteLine( vbCrLf & timeFmt, _
"Current date and time:", currentDate )
Console.WriteLine( dataFmt, "Daylight saving time?", _
localZone.IsDaylightSavingTime( currentDate ) )

' Get the current Coordinated Universal Time (UTC) and UTC
' offset.
Dim currentUTC As DateTime = _
localZone.ToUniversalTime( currentDate )
Dim currentOffset As TimeSpan = _
localZone.GetUtcOffset( currentDate )

Console.WriteLine( timeFmt, "Coordinated Universal Time:", _
currentUTC )
Console.WriteLine( dataFmt, "UTC offset:", currentOffset )

' Get the DaylightTime object for the current year.
Dim daylight As DaylightTime = _
localZone.GetDaylightChanges( currentYear )

' Display the daylight saving time range for the current year.
Console.WriteLine( vbCrLf & _
"Daylight saving time for year {0}:", currentYear )
Console.WriteLine( "{0:yyyy-MM-dd HH:mm} to " & _
"{1:yyyy-MM-dd HH:mm}, delta: {2}", _
daylight.Start, daylight.End, daylight.Delta )
End Sub
End Module

'This example of selected TimeZone class elements generates the following
'output, which varies depending on the time zone in which it is run.
'Standard time name: Pacific Standard Time
'Daylight saving time name: Pacific Daylight Time
'Current date and time: 2006-01-06 16:47
'Daylight saving time? False
'Coordinated Universal Time: 2006-01-07 00:47
'UTC offset: -08:00:00
'Daylight saving time for year 2006:
'2006-04-02 02:00 to 2006-10-29 02:00, delta: 01:00:00

Question 2
Have a problem with negative serial values and don't want to use text or 1904. Need to convert time/day in one city to time/day in other city. Works on positive value but not negative i.e. GMT -9. Any suggestions?
Could really do with the help. Spent a day looking for answers. Thanks

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...