Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Is a specific day EST or EDT (daylight saving) using Windows API

Posted on 2014-12-31
6
Medium Priority
?
176 Views
Last Modified: 2015-01-02
I need a GmtToObservedTime function that will convert an Eastern Standard Time date to the "Observed Date".

For instance in New York City on Sunday 11/2/2014 I sent email #1 at 5:55 GMT and one hour later email #2 was sent at 6:55 GMT. My watch was set to Eastern Standard Time, so email #1 looked to me like it went out at 1:55 AM.

At 2:30 AM that Sunday morning I  reset my watch to "fall back". So, it looked to me like email #2 WAS ALSO SENT AT 1:55.

I want my routine to run now (in December), but to show the same thing my watch showed that morning. In other words both emails should show as being sent at 1:55 AM Sunday 11/2/2014.

I want to use a windows api so that my routine will work for many years.

I modified the following code from http://www.cpearson.com/excel/LocalAndGMTTimes.htm and it  will "work" for the next few years.

How can I replace the hard-coded date ranges near line 70  with an API call, so the code will work for many years?

Option Explicit
' Private Const TIME_ZONE_ID_UNKNOWN = 0
' Private Const TIME_ZONE_ID_STANDARD = 1
' Private Const TIME_ZONE_ID_DAYLIGHT = 2
' Private Const TIME_ZONE_ID_INVALID = -1
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 Declare Function GetTimeZoneInformation Lib "kernel32" ( _
    lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long



Public Function GMTToLocaltime(GMT As Date) As Date
' this converts GMT to LocalTime.
' When run in eastern USA during December  #6/1/2014 8:00:00 AM# WILL BECOME #6/1/2014 3:00:00 PM#
' When run in eastern USA during July      #6/1/2014 8:00:00 AM# WILL BECOME #6/1/2014 4:00:00 PM#
'

 Const TIME_ZONE_ID_DAYLIGHT = 2
Dim TZI As TIME_ZONE_INFORMATION
Dim IsRunningDST As Boolean

If GetTimeZoneInformation(TZI) = TIME_ZONE_ID_DAYLIGHT Then IsRunningDST = True

GMTToLocaltime = GMT - time(0, TZI.Bias, 0) + time(Abs(IsRunningDST), 0, 0)
End Function




Public Function GMTToObservedtime(GMT As Date) As Date
' this converts GMT to LocalTime as it was being observed on my computer AT THAT TIME.
'
' for instance on Sunday 11/2/2014 I sent email #1 at 5:55 GMT and one hour later email #2 was sent at 6:55 GMT
' I reset my watch that Sunday so my watch showed that both emails were sent at 1:55.
' Today, I run the following on a computer with Eastern Time Zone. I should see the following
'   Debug.Print GMTToObservedtime(#3/9/2014 6:55:00 AM#) ' should show  #3/9/2014 1:55:00#)
'   Debug.Print GMTToObservedtime(#3/9/2014 7:55:00 AM#) ' should show  #3/9/2014 3:55:00#)

'   Debug.Print GMTToObservedtime(#11/2/2014 5:55:00 AM#) ' should show  #11/2/2014 1:55:00#)
'   Debug.Print GMTToObservedtime(#11/2/2014 6:55:00 AM#) ' should show  #11/2/2014 1:55:00#)

Dim TZI As TIME_ZONE_INFORMATION
Dim UseDst As Long

Call GetTimeZoneInformation(TZI)


GMTToObservedtime = GMT - TimeSerial(0, TZI.Bias, 0)
Select Case GMTToObservedtime
    Case #3/10/2013 2:00:00 AM# To #11/3/2013 1:00:00 AM#: Stop: UseDst = 1
    Case #3/9/2014 2:00:00 AM# To #11/2/2014 1:00:00 AM#: Stop: UseDst = 1
    Case #3/8/2015 2:00:00 AM# To #11/1/2015 1:00:00 AM#: Stop: UseDst = 1
    Case #3/13/2016 2:00:00 AM# To #11/6/2016 1:00:00 AM#: Stop: UseDst = 1
    Case #3/12/2017 2:00:00 AM# To #11/5/2017 1:00:00 AM#: Stop: UseDst = 1

End Select
GMTToObservedtime = GMTToObservedtime + TimeSerial(UseDst, 0, 0)
End Function
Sub t605()
Debug.Print GMTToObservedtime(#3/9/2014 6:55:00 AM#) ' should show  #3/9/2014 1:55:00#)
Debug.Print GMTToObservedtime(#3/9/2014 7:55:00 AM#) ' should show  #3/9/2014 3:55:00#)

Debug.Print GMTToObservedtime(#11/2/2014 5:55:00 AM#) ' should show  #11/2/2014 1:55:00#)
Debug.Print GMTToObservedtime(#11/2/2014 6:55:00 AM#) ' should show  #11/2/2014 1:55:00#)

End Sub

Open in new window


For giggles, you might also want to look at

more-falsehoods-programmers-believe-about-time

For instance, I learned that it’s "daylight saving time" and not "daylight savings time". I’ve been saying it wrong my whole life!
0
Comment
Question by:rberke
  • 3
  • 2
6 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 40526354
For now in the US, DST starts on the second Sunday in March and ends on the first Sunday in November.

mlmcc
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 1200 total points
ID: 40526358
Here is a link to a full package of date and time related functions

http://www.cpearson.com/excel/DaylightSavings.htm

It was written by a Chip Pearson and he provides a link to download the source code.

mlmcc
0
 
LVL 36

Accepted Solution

by:
Miguel Oz earned 800 total points
ID: 40526916
Any particular reason to use GMT. The  preferred option is to use UTC. and then use Tolocaltime to convert your UTC time to local time. Basically it uses the time zone of the computer you are running to make the conversion.

If you require to display date time in different time zones please check Converting Times Between Time Zones article on how to convert your required local time to UTC and back to a different time zone.

Note: There is no need to use API as .NET framework has classes to help you dealing with date time and time zone.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Author Comment

by:rberke
ID: 40527155
"Observed time" is my own concept, but I wonder if there is a more formal name.

Converting UTC to localtime does not match the ObservedTime concept.

GMTToObservedtime(#3/9/2014 7:55:00 AM#) ' should show  #3/9/2014 3:55:00#) whereas
GMTToLocalTime(#3/9/2014 7:55:00 AM#) ' would show  #3/9/2014 2:55:00#)


The DST rules cited by the experts will work for my needs, for instance TransitionDateDstToStandard(YYYY As Integer) in the Pearson routines.

But, what if I wanted the routine to work in other timezones? For instance daylight time in southern hemisphere and europe have different transitions.

that is why I expected to find an API to handle mutliple time zones.
Also, I am using vba, not .net.
0
 
LVL 5

Author Comment

by:rberke
ID: 40527746
When I get some more time, I will convert the following vb code into vba.  For right now, I will simply use TransitionDateDstToStandardYYYY code from the cpearson link.

Thanks for everybody's help.

Bob

Try
   Dim cstZone As TimeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time")
   Dim cstTime As Date = TimeZoneInfo.ConvertTimeFromUtc(timeUtc, cstZone)
   Console.WriteLine("The date and time are {0} {1}.", _
                     cstTime, _
                     IIf(cstZone.IsDaylightSavingTime(cstTime), _
                         cstZone.DaylightName, cstZone.StandardName))
Catch e As TimeZoneNotFoundException
   Console.WriteLine("The registry does not define the Central Standard Time zone.")
Catch e As InvalidTimeZoneException
   Console.WriteLine("Registry data on the Central Standard Time zone has been corrupted.")
End Try

rberke
0
 
LVL 5

Author Closing Comment

by:rberke
ID: 40527757
mimcc gets more points, because he has the solution I used.

Miguel gets "best solution" because he gave a link to a vb routine that will work for many different time zones.  The only reason I didn't use that link is that I don't have time to convert it from vb to vba.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question