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

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!
LVL 5
rberkeConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
For now in the US, DST starts on the second Sunday in March and ends on the first Sunday in November.

mlmcc
0
mlmccCommented:
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
Miguel OzSoftware EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

rberkeConsultantAuthor Commented:
"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
rberkeConsultantAuthor Commented:
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
rberkeConsultantAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.