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

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


"Charlotte E." <@ wrote in message
...
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

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


"Peter T" <peter_t@discussions wrote in message
...
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


"Charlotte E." <@ wrote in message
...
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

One way,

Returns the second sunday for any valid date in a1

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

Mike

"Charlotte E." wrote:

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 wrote:
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


"Peter T" <peter_t@discussions wrote in message
...
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


"Charlotte E." <@ wrote in message
...
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

Thanks :-)


Peter T wrote:
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


"Peter T" <peter_t@discussions wrote in message
...
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


"Charlotte E." <@ wrote in message
...
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