Find First Sunday of month chosen function

**Ruegen** · 02-13-2014, 03:47 PM

I've had a look through the vba functions

I know I can get the day of a month but is there a way of getting the first Sunday of a chosen month?

I'm constructing a daylight savings clock that switches over some times at 2pm on the first Sunday of April. (click here to see why)

Is there a simple way to do this?

**June7** · 02-13-2014, 03:58 PM

Bing: VBA first Monday of month

http://www.tek-tips.com/viewthread.cfm?qid=1290617

Adjust code as required for whichever day is desired.

**Ruegen** · 02-13-2014, 04:17 PM

Thanks

Originally Posted by June7

Bing: VBA first Monday of month

http://www.tek-tips.com/viewthread.cfm?qid=1290617

Adjust code as required for whichever day is desired.

Would it be possible to compare the current date with a less than or greater than first sunday of that month?

say if date() < FirstSunday1 then
do this
elseif date() > FirstSunday1 but not greater than FirstSunday2 (because it changes a second time).

etc

how do you think I should tackle this?

**June7** · 02-13-2014, 04:55 PM

Why April? In the U.S., DST in 2014 changes on March 9 and November 2.

According to Wiki:

Starting in 2007, most of the United States and Canada observe DST from the second Sunday in March to the first Sunday in November, almost two-thirds of the year.^[41] The 2007 US change was part of the Energy Policy Act of 2005; previously, from 1987 through 2006, the start and end dates were the first Sunday in April and the last Sunday in October, and Congress retains the right to go back to the previous dates now that an energy-consumption study has been done.

**Xipooo** · 02-13-2014, 05:29 PM

How about everyone just moves to Arizona where there is no DST. Problem solved.

**Ruegen** · 02-13-2014, 05:33 PM

Originally Posted by Xipooo

How about everyone just moves to Arizona where there is no DST. Problem solved.

lol

To answer June7 - I have no idea.

All I know is that in Australia we put the clock forward or backwards depending on what Australian State it is (as mentioned in my link)

I've got a function

Code:

Dim cDateTime As Date
cDateTime = Date


If [StateID] = 4 Then
cDateTime = DateAdd("h", -1, Time())
ElseIf [StateID] = 5 Then
cDateTime = DateAdd("n", -30, Time())
ElseIf [StateID] = 8 Then
cDateTime = DateAdd("h", -3, Time())
ElseIf [StateID] = 3 Then
cDateTime = DateAdd("n", -90, Time())
Else
cDateTime = Time()
End If


Me.txtTimeBox.Value = cDateTime

I plan to make another IF statement with the times for when it turns back - then I will wrap another IF around those two depending on if it is after or before one of those two dates (the first sunday)

that way if it is after that sunday all the states times displayed on the form when they change record will change.

**June7** · 02-13-2014, 05:51 PM

Okay, running down under, I did not notice the link before.

So I guess you need to calculate the first Sunday for April and November of the current and test if the current date falls between.

**Ruegen** · 02-13-2014, 06:11 PM

Originally Posted by June7

Okay, running down under, I did not notice the link before.

So I guess you need to calculate the first Sunday for April and November of the current and test if the current date falls between.

ah brilliant

**Ruegen** · 02-13-2014, 06:38 PM

ok... what have I missed.. it's not quite working.

Code:

Dim cDateTime As Date
Dim FirstSunday1 As Date
Dim FirstSunday2 As Date
Dim d As Date
Dim w As Integer


d = DateSerial(Year(Date), 4, 1)
w = Weekday(d, vbSunday)
FirstSunday1 = d + IIf(w, 8 - w, 0)


d = DateSerial(Year(Date), 10, 1)
w = Weekday(d, vbSunday)
FirstSunday2 = d + IIf(w, 8 - w, 0)


cDateTime = Date


If cDateTime >= FirstSunday1 And cDateTime <= FirstSunday2 Then


    If [StateID] = 4 Then
    cDateTime = Time()
    ElseIf [StateID] = 5 Then
    cDateTime = DateAdd("n", -30, Time())
    ElseIf [StateID] = 8 Then
    cDateTime = DateAdd("h", -2, Time())
    ElseIf [StateID] = 3 Then
    cDateTime = DateAdd("n", -30, Time())
    Else
    cDateTime = Time()
    End If


ElseIf cDateTime >= FirstSunday2 And cDateTime <= FirstSunday1 Then


    If [StateID] = 4 Then
    cDateTime = DateAdd("h", -1, Time())
    ElseIf [StateID] = 5 Then
    cDateTime = DateAdd("n", -30, Time())
    ElseIf [StateID] = 8 Then
    cDateTime = DateAdd("h", -3, Time())
    ElseIf [StateID] = 3 Then
    cDateTime = DateAdd("n", -90, Time())
    Else
    cDateTime = Time()
    End If
End If


Me.txtTimeBox.Value = cDateTime

**June7** · 02-13-2014, 07:48 PM

Why isn't it working, what happens - error message, wrong results, nothing?

Have you seen Allen Browne's article about international dates http://allenbrowne.com/ser-36.html

**Ruegen** · 02-13-2014, 10:05 PM

Originally Posted by June7

Why isn't it working, what happens - error message, wrong results, nothing?

Have you seen Allen Browne's article about international dates http://allenbrowne.com/ser-36.html

yeah that may be the problem...

**ssanfu** · 02-14-2014, 08:06 PM

I prefer using the "Select Case" construct instead if all of the IF statements.

Code:

Dim TimeOffset As Integer
Dim strInterval As String

'--------------------------------
   TimeOffset = 0
   strInterval = ""

   'set default time
   cDateTime = Time()

   If cDateTime >= FirstSunday1 And cDateTime <= FirstSunday2 Then
      Select Case [StateID]
         Case 3
            TimeOffset = -30
            strInterval = "n"
         Case 4
            TimeOffset = 0
            strInterval = ""
         Case 5
            TimeOffset = -30
            strInterval = "n"
         Case 8
            TimeOffset = -2
            strInterval = "h"
      End Select
   ElseIf cDateTime >= FirstSunday2 And cDateTime <= FirstSunday1 Then
      Select Case [StateID]
         Case 3
            TimeOffset = -90
            strInterval = "n"
         Case 4
            TimeOffset = -1
            strInterval = "h"
         Case 5
            TimeOffset = -30
            strInterval = "n"
         Case 8
            TimeOffset = -3
            strInterval = "h"
      End Select
   End If

   If strInterval <> "" Then
      cDateTime = DateAdd(strInterval, TimeOffset, Time())
   End If
   
   Me.txtTimeBox.Value = cDateTime

Just a suggestion... feel free to ignore me

**Ruegen** · 02-15-2014, 09:57 PM

Originally Posted by ssanfu

I prefer using the "Select Case" construct instead if all of the IF statements.

Code:

Dim TimeOffset As Integer
Dim strInterval As String

'--------------------------------
   TimeOffset = 0
   strInterval = ""

   'set default time
   cDateTime = Time()

   If cDateTime >= FirstSunday1 And cDateTime <= FirstSunday2 Then
      Select Case [StateID]
         Case 3
            TimeOffset = -30
            strInterval = "n"
         Case 4
            TimeOffset = 0
            strInterval = ""
         Case 5
            TimeOffset = -30
            strInterval = "n"
         Case 8
            TimeOffset = -2
            strInterval = "h"
      End Select
   ElseIf cDateTime >= FirstSunday2 And cDateTime <= FirstSunday1 Then
      Select Case [StateID]
         Case 3
            TimeOffset = -90
            strInterval = "n"
         Case 4
            TimeOffset = -1
            strInterval = "h"
         Case 5
            TimeOffset = -30
            strInterval = "n"
         Case 8
            TimeOffset = -3
            strInterval = "h"
      End Select
   End If

   If strInterval <> "" Then
      cDateTime = DateAdd(strInterval, TimeOffset, Time())
   End If
   
   Me.txtTimeBox.Value = cDateTime

Just a suggestion... feel free to ignore me

Yeah, your method is a love cleaner to use. I have tried it too and I still get no change - maybe I have it on the wrong event? I am changing records from one to the next and it should update each record.

I haven't formatted the date field in any way so it shouldn't need to be reformatted.....?

I have

Code:





Function DaylightSavings()




Dim cdatetime As Date
Dim TimeOffset As Integer
Dim strInterval As String
Dim FirstSunday1 As Date
Dim FirstSunday2 As Date
Dim d1 As Date
Dim w1 As Integer
Dim d2 As Date
Dim w2 As Integer


'--------------------------------
   TimeOffset = 0
   strInterval = ""




d1 = DateSerial(Year(Date), 4, 1)
w1 = Weekday(d1, vbSunday)
FirstSunday1 = d1 + IIf(w1, 8 - w1, 0)






d2 = DateSerial(Year(Date), 10, 1)
w2 = Weekday(d2, vbSunday)
FirstSunday2 = d2 + IIf(w2, 8 - w2, 0)






   'set default time
'--------------------------------
   TimeOffset = 0
   strInterval = ""


   'set default time
  cdatetime = Date


   If cdatetime >= FirstSunday1 And cdatetime < FirstSunday2 Then
      Select Case StateID
        Case 3
            TimeOffset = -30
            strInterval = "n"
         Case 4
            TimeOffset = 0
            strInterval = ""
         Case 5
            TimeOffset = -30
            strInterval = "n"
         Case 8
            TimeOffset = -2
            strInterval = "h"
      End Select
ElseIf cdatetime >= FirstSunday2 And cdatetime < FirstSunday1 Then
      Select Case StateID
         Case 3
            TimeOffset = -90
            strInterval = "n"
         Case 4
            TimeOffset = -1
            strInterval = "h"
         Case 5
            TimeOffset = -30
            strInterval = "n"
         Case 8
            TimeOffset = -3
            strInterval = "h"
      End Select
   End If




   If strInterval <> "" Then
        cdatetime = Time()
      cdatetime = DateAdd(strInterval, TimeOffset, Time())
      Else
        cdatetime = Time()
   End If
   
   Me.txtTimeBox.Value = cdatetime

**Ruegen** · 02-16-2014, 12:32 AM

I think this is correct (had to fix the >= etc)

Code:




Dim cdatetime As Date
Dim TimeOffset As Integer
Dim strInterval As String
Dim FirstSunday1 As Date
Dim FirstSunday2 As Date
Dim d1 As Date
Dim w1 As Integer




'--------------------------------
   TimeOffset = 0
   strInterval = ""




d = DateSerial(Year(Date), 4, 1)
w = Weekday(d, vbSunday)
FirstSunday1 = d + IIf(w, 8 - w, 0)






d = DateSerial(Year(Date), 10, 1)
w = Weekday(d, vbSunday)
FirstSunday2 = d + IIf(w, 8 - w, 0)








   'set default time
'--------------------------------
   TimeOffset = 0
   strInterval = ""


   'set default time
  cdatetime = Date




   If ("#" & cdatetime & "#" > "#" & FirstSunday1 & "#") And ("#" & cdatetime & "#" >= "#" & FirstSunday2 & "#") Then
      Select Case StateID
Case 3
            TimeOffset = -30
            strInterval = "n"
Case 4
            TimeOffset = 0
            strInterval = ""
Case 5
            TimeOffset = -30
            strInterval = "n"
     Case 8
            TimeOffset = -2
            strInterval = "h"
            
         
      End Select
         
ElseIf "#" & cdatetime & "#" < "#" & FirstSunday2 & "#" And "#" & cdatetime & "#" <= "#" & FirstSunday1 & "#" Then
      Select Case StateID
Case 3
            TimeOffset = -90
                strInterval = "n"
 Case 4
            TimeOffset = -1
            strInterval = "h"
Case 5
            TimeOffset = -30
            strInterval = "n"
Case 8
            TimeOffset = -3
            strInterval = "h"
            
     End Select
    
  End If




   If strInterval <> "" Then


      cdatetime = DateAdd(strInterval, TimeOffset, Time())
      Else
      cdatetime = Time()
      
   End If
   
   Me.txtTimeBox.Value = cdatetime

**Ruegen** · 04-06-2014, 11:29 PM

eehhh I have to bring this one back

didn't work as expected

I now have to figure out why it isn't working

Code:


   If "#" & cdatetime & "#" < "#" & FirstSunday1 & "#" Then




   Select Case intState
    Case 3
            TimeOffset = -90
            strInterval = "n"
     Case 4
            TimeOffset = -1
            strInterval = "h"
    Case 5
            TimeOffset = -30
            strInterval = "n"
    Case 8
            TimeOffset = -3
            strInterval = "h"
            
     End Select


Forms!frmSchools!box1 = 1
         
ElseIf "#" & cdatetime & "#" < "#" & FirstSunday1 & "#" And "#" & cdatetime & "#" <= "#" & FirstSunday2 & "#" Then


     
          Select Case intState
    Case 3
            TimeOffset = -30
            strInterval = "n"
            
     Case 4
            TimeOffset = 0
            strInterval = "n"


    Case 5
            TimeOffset = -30
            strInterval = "n"
     Case 8
            TimeOffset = -2
            strInterval = "h"


      End Select
Forms!frmSchools!box1 = 2


ElseIf "#" & cdatetime & "#" < "#" & FirstSunday2 & "#" And "#" & cdatetime & "#" <= "#" & FirstSunday3 & "#" Then


       Select Case intState
    Case 3
            TimeOffset = -90
                strInterval = "n"
     Case 4
            TimeOffset = -1
            strInterval = "h"
    Case 5
            TimeOffset = -30
            strInterval = "n"
    Case 8
            TimeOffset = -3
            strInterval = "h"
            
     End Select
Forms!frmSchools!box1 = 3

always goes to the third if statement...

Find First Sunday of month chosen function

Thread Tools

Find First Sunday of month chosen function

Similar Threads

Use DLookup to autofill values in fields, based on value chosen by Select function

To find Month and Days in given date period

How do I create a search function that then takes you to the chosen record?

Find a month for each Name.

Find # of Sundays in a month

Posting Permissions