Simple function to Check if date is DST (Daylight Saving Time)

[MyTech]

Here is code to check any given date if it is within the DST (Daylight Saving Time) period or not.

It returns True for "Daylight Saving Time", and False for "Standard Time" (non DST).


It is based on the DST rules of USA. If you don't know how to change it to your country rules, you can reply here.

Public Function dstCheck(ByVal vDate As Date) As Boolean

Dim dstStart As Date, dstEnd As Date

dstStart = "March 8," & year(vDate)
Do Until Weekday(dstStart) = 1
    dstStart = dstStart + 1
Loop

dstEnd = "November 1," & year(vDate)
Do Until Weekday(dstEnd) = 1
    dstEnd = dstEnd + 1
Loop

dstCheck = vDate >= dstStart And vDate < dstEnd

End Function

 

[MarkK]

Public Function IsDST(ByVal d0 As Date) As Boolean
   IsDST = d0 >= NextSun("Mar 8 " & year(d0)) And d0 < NextSun("Nov 1 " & year(d0))
End Function

Private Function NextSun(d1 As Date) As Date
   NextSun = d1 + 7 - Weekday(d1)
End Function

 

[MyTech]

Correct, much shorter. (Hmm.. but not 'simpler' though.. (there is no option of 'stubborn' face. lol.) )

 

[Galaxiom]

Lagbolts code looks a lot simpler to me. Looping as in the original suggestion is the coding equivalent of counting on fingers.

 

[MyTech]

Yeah.

As long I glanced - but not examined - Lagbolt's code, mine 'looked' to 'me' simpler. I'm actually changing my code to his.

Admit.

 

[MarkK]

The main thing, I think, is to avoid the loops, so this is really the main optimization...

Sunday = AnyDate + 7 - Weekday(AnyDate)

...to calculate the Sunday.
Cheers,
Mark

 

[Sleepy]

Hi,

I like this solution but I have a question for when I call this function.

Public Function IsDST(ByVal d0 As Date)

-What values(variable values) do I put in brackets if any. Do I need a value for ByVal and d0?

I had trouble figuring that out from the post.

 

[Beetle]

You only need to supply a date value for d0. ByVal is not an argument of the function it just determines how the argument (or value) is passed. It would look like;

IsDST(05/19/2014)

 

[Galaxiom]

That would be:
IsDST(#05/19/2014#)

 

[Beetle]

True. Thanks for correcting my oversight.

 

[Sleepy]

Hi,

Thanks guys. I guess I have to do some study on the ByVal code.
Good timing as I just fixed what was wrong with the function calls I was making re dst.
Good ol' windows lost my "Time_Zone_Information" value so my functions were returning a zero. (So none worked) Fix was changing to another time zone and then changing back to mine. Your function (that I asked about) will work now too.
Appreciate the quick reply.

Sleepy

 

[DacePara]

Hi,

What would it be for the UK.

Last sunday in march & last Sunday in October.

many thanks

Dace

 

[Danick]

I'm trying to use this code on a form where I'd like to display if the local time in that country is in DST or not. I have 2 text boxes and an unbound check box
DSTSTART - bound to a DSTSTART field in a table
DSTEND - bound to a DSTEND field in a table
DST Check box - Unbound

The idea is to have the checkbox show if the two dates are in the Daylight Savings Time.
Something like this, but I can't seem to get it to work.

IsDST = d0 >= NextSun([DSTSTART]  & Year(d0)) And d0 < NextSun([DSTEND]  & Year(d0))

And the check box would do a calculation like

IsDST Now()

Any ideas?

 

[MarkK]

...but I can't seem to get it to work.

You need to elaborate on this. To troubleshoot, we need to know the specific symptoms of the failure.
hth
Mark

 

[Danick]

You need to elaborate on this. To troubleshoot, we need to know the specific symptoms of the failure.
hth
Mark

I get a Run-time error '13' type mismatch.
It's just one table, one module with the function and a form.The module has

Public Function IsDST(ByVal d0 As Date) As Boolean
   IsDST = d0 >= NextSun("DSTSTART " & Year(d0)) And d0 < NextSun("DSTEND " & Year(d0))
End Function

Private Function NextSun(d1 As Date) As Date
   NextSun = d1 + 7 - Weekday(d1)
End Function

The form has
DSTSTART text box
DSTEND text box
checkbox with the control source:

=IsDST(Now())

The table has a country id field and text fields with dates like the one in this thread:
03/11 for DSTSTART and 11/04 for DSTEND

I get the error code as soon as I open the form.
Please see the attached database

 

[MarkK]

That code you are using is just confusing you. You should back up and think about what you have, and what you need to do with it first.

In each row, you have the start and end date of the DST period. So to calculate if today is in that period, what is the expression you need to write? What are the questions you need to answer?

TodayIsDST = Is StartDate < Today, AND Is Today < EndDate

See what's happening in that expression? I would think of it that way.

This code you are trying to re-purpose, namely that IsDST() function, only receives ONE date as a parameter. The function you need has to receive THREE dates, the start, the end, and the day to test.

hth
Mark

 

[Danick]

Actually, I found another way of doing this instead of using the IsDst function.
Basically, I created a checkbox in the table called ChkDST.
Then I used an after update VBA to change the state of the checkbox depending on the values in the DSTStart and DSTEnd fields. This is what I came up with. Seems to be working although I haven't tested it in real world yet. Let me know what you think...

Private Sub DSTEND_AfterUpdate()
If IsNull(Me.DSTSTART) Then
Me.ChkDST = False
Exit Sub
Else
If Now() >= (DateValue([DSTSTART] & "/" & Year(Date))) And Now() < (DateValue([DSTEND] & "/" & Year(Date))) Then
Me.ChkDST = True
Else
Me.ChkDST = False
End If
End If
End Sub

Private Sub DSTSTART_AfterUpdate()
If IsNull(Me.DSTSTART) Then
Me.ChkDST = False
Exit Sub
Else
If Now() >= (DateValue([DSTSTART] & "/" & Year(Date))) And Now() < (DateValue([DSTEND] & "/" & Year(Date))) Then
Me.ChkDST = True
Else
Me.ChkDST = False
End If
End If
End Sub

 

[MarkK]

Does it work? What do you think?
Mark

 

[MarkK]

I would do it more like...

Private Sub DSTEND_AfterUpdate()
    SetDST
End Sub

Private Sub DSSTART_AfterUpdate()
    SetDST
End Sub

Private Sub Form_Current()
    SetDST
End Sub
    
Private Sub SetDST()
    If IsDate(Me.dststart) And IsDate(Me.dstend) Then
        Me.chkdst = IsDst(Me.dststart, Me.dstend)
    Else
        Me.chkdst = False
    End If
End Sub

Private Function IsDst(d1 As Date, d2 As Date) As Boolean
    IsDst = d1 < Now And Now < d2
End Function

hth
Mark

 

[Sabin]

Hi,

What would it be for the UK.

Last sunday in march & last Sunday in October.

many thanks

Dace

This set of functions will work for any country including UK

Function IsDST(DateCheck As Date, StartMonth As Integer, StartWeek As Integer, EndMonth As Integer, EndWeek As Integer, DOW_EN As String) As Boolean
'DO NOT REMOVE
'It takes nothing away from what you do
'Gives me credit for creating an International Daylight Saving Time Routine
'
'Michel Sabourin (c)2018
'mpsabourin@gmail.com
'
'Will be true if DST is active on specified date given the DST rules for your State/Country
'
    Dim Param As Boolean, StartDateDST As Date, EndDateDST As Date
    Param = True
    If Not IsDate(DateCheck) Then Param = False
    If StartMonth < 1 Or StartMonth > 12 Then Param = False
    If StartWeek < 1 Or StartWeek > 5 Then Param = False
    If EndMonth < 1 Or EndMonth > 12 Then Param = False
    If EndWeek < 1 Or EndWeek > 5 Then Param = False
    DOW_EN = UCase(DOW_EN)
    If DOW_EN <> "SATURDAY" And DOW_EN <> "SUNDAY" Then Param = False
    If Not Param Then
        MsgBox "IsDST(DateCheck As Date, StartMonth As Integer, StartWeek As Integer, EndMonth As Integer, EndWeek As Integer, DOW_EN As String) As Boolean" _
        & Chr(10) & "DateCheck = Today's date or Date being checked" _
        & Chr(10) & "StartMonth & EndMonth = Whole number (1 - 12) start of DST and end of DST" _
        & Chr(10) & "StartWeek & EndWeek = Whole number (1 - 5) = 1st, 2nd, 3rd, 4th or 5= LAST" _
        & Chr(10) & "Changeover Day of Week = ""Saturday"" or ""Sunday""" _
        , vbOKOnly, "USAGE"
        IsDST = Null
    Else
        StartDateDST = NextDOW(DateSerial(Year(DateCheck), StartMonth, FirstPotentialDate(Year(DateCheck), StartMonth, StartWeek)), DOW_EN)
        EndDateDST = NextDOW(DateSerial(Year(DateCheck), EndMonth, FirstPotentialDate(Year(DateCheck), EndMonth, EndWeek)), DOW_EN)
        IsDST = DateCheck >= StartDateDST And DateCheck < EndDateDST
    End If
End Function
 
Function NextDOW(MyPotentialDate As Date, DOW_EN As String) As Date
'DO NOT REMOVE
'It takes nothing away from what you do
'Gives me credit for creating an International Daylight Saving Time Routine
'
'Michel Sabourin (c)2018
'mpsabourin@gmail.com
'
    'Next Date from Potential start for that particular date
    Dim MyWeekDay As Integer
    DOW_EN = UCase(DOW_EN)
    If Not IsDate(MyPotentialDate) Then DOW_EN = ""
    Select Case DOW_EN
    Case "SUNDAY"
        NextDOW = MyPotentialDate + 7 - Weekday(MyPotentialDate, vbMonday)
    Case "SATURDAY"
        NextDOW = MyPotentialDate + 7 - Weekday(MyPotentialDate, vbSunday)
    Case Else
        MsgBox "NextDOW(MyDate As Date, DOW_EN As String) As Date" _
        & Chr(10) & "MyDate = First Potential Date" _
        & Chr(10) & """Saturday"" or ""Sunday""" _
        , vbOKOnly, "USAGE"
        NextDOW = Null
    End Select
End Function
 
Function FirstPotentialDate(MyYear As Integer, MyMonth As Integer, MyWeek As Integer) As Integer
'DO NOT REMOVE
'It takes nothing away from what you do
'Gives me credit for creating an International Daylight Saving Time Routine
'
'Michel Sabourin (c)2018
'mpsabourin@gmail.com
'
    If MyWeek < 5 Then
        FirstPotentialDate = 1 + 7 * (MyWeek - 1)
    Else
        FirstPotentialDate = Day(DateSerial(MyYear, (MyMonth \ 12) + 1, 1) - 7)
    End If
End Function

Contact if you wish an Excel (.xlsm) file example

Enjoy!