Second Sunday in the month of May?

[Charlotte E.]

Can anyone give me a VBA formalua, which will return the date of the second
Sunday in the month of May, given any year?


TIA,

CE

 

[Peter T]

One way

=15-WEEKDAY(DATE(A1,5,0),1)

or to return the full date

=DATE(A1,5,15-WEEKDAY(DATE(A1,5,0),1))

where A1 contains the year

Regards,
Peter T

 

[Peter T]

Afraid that worksheet formula is wrong if the 2nd Sunday is the 15th, an
easy fix. But I notice now you asked for VBA

Function May2ndSunday(yr) As Date
Dim dy As Long
yr = CLng(yr)
dy = 15 - Weekday(DateSerial(yr, 5, 0))
May2ndSunday = DateSerial(yr, 5, dy)
End Function

Regards,
Peter T

 

[Mike H]

One way,

Returns the second sunday for any valid date in a1

=DATE(YEAR(A1),MONTH(A1),7+CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),1,0,6,5,4,3,2))

Mike

 

[Charlotte E.]

Afraid that worksheet formula is wrong if the 2nd Sunday is the 15th,
an easy fix. But I notice now you asked for VBA

Function May2ndSunday(yr) As Date
Dim dy As Long
yr = CLng(yr)
dy = 15 - Weekday(DateSerial(yr, 5, 0))
May2ndSunday = DateSerial(yr, 5, dy)
End Function

Regards,
Peter T

 

[Charlotte E.]

Thanks

Afraid that worksheet formula is wrong if the 2nd Sunday is the 15th,
an easy fix. But I notice now you asked for VBA

Function May2ndSunday(yr) As Date
Dim dy As Long
yr = CLng(yr)
dy = 15 - Weekday(DateSerial(yr, 5, 0))
May2ndSunday = DateSerial(yr, 5, dy)
End Function

Regards,
Peter T