Second Sunday in the month of May?

C

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
 
P

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
 
P

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
 
M

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
 
C

Charlotte E.

Peter said:
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
Click to expand...
 
C

Charlotte E.

Thanks :)


Peter said:
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
Click to expand...
 
Top