Is Daylight Savings Time in Effect?

PJ wondered if there is a way, in a macro, to determine whether the system time on a machine is daylight savings time or not. The answer is that you can find it out, but it is not a trivial task.

If you are interested in an approach that is based on your local machine, then you need to make calls to the Windows API. Rather than re-invent the wheel, information on how to do this can be found in the detailed explanation by Chip Pearson at this site:

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

If you are sure that your machine will have access to the Internet at the time that you need to know about Daylight Savings Time, you could also do some comparisons with information you get from the National Institutes of Standards and Technology (NIST) concerning the current time.

You can, over the Web, go to an NIST site that will return information about the current time. The URL to use is similar to this one:

http://nist.time.gov/timezone.cgi?Eastern/d/-5

In this case, the time returned will be in the Eastern time zone, which is five hours before the standard universal time. Using Excel's Web Query capabilities, you can access the information returned by the URL and then compare it to the time on the local machine.

Public Sub DSTorST()
    Dim myWksht As Worksheet
    Dim sTime As Date, nTime As Date
    Dim sST As String, sDST As String, answer As String
    Dim absDif As Variant
    Dim myT As Integer
    Dim sURL As String

    Set myWksht = ActiveSheet
    sURL = "http://nist.time.gov/timezone.cgi?Eastern/d/-5"
    myT = 5

    With myWksht.QueryTables.Add("URL;";sURL, myWksht.Range("A1"))
        .Refresh
    End With

    On Error Resume Next
    Application.ScreenUpdating = False
    sST = "Your computer system displays Standard Time"
    sDST = "Your computer system displays Daylight Savings Time"
    sTime = Now()
    sTime = TimeValue(sTime)
    nTime = Range("B3").Value
    absDif = Abs(sTime - nTime)
    If (absDif > (myT * 0.000694444) And _
      WorksheetFunction.IsNumber(WorksheetFunction.Find _
      ("Not Daylight", D11)) = True) Or (absDif <= _
      (myT * 0.000694444) And WorksheetFunction.IsNumber _
      (WorksheetFunction.Find("Not Daylight", D11)) = False) _
      Then
        answer = sST
    Else
        answer = sDST
    End If

    myWksht.UsedRange.Select
    Selection.Delete
    Application.ScreenUpdating = True
    MsgBox answer, , "System Time On Your Machine"
End Sub

The macro compares the official time gathered by the query to the system time on the local machine. A tolerance of +/-5 minutes is assumed, as specified in the myT variable.

If the absolute difference between the two values is <= 5 min and NIST time is not DST or, if the difference is > 5 min and NIST time is DST, then the system time = Standard Time. Otherwise the system time = Daylight Savings Time.