Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 70
Default Second Sunday in the month of May?

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


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default Second Sunday in the month of May?

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




  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default Second Sunday in the month of May?

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






  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,501
Default Second Sunday in the month of May?

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



  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 70
Default Second Sunday in the month of May?

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





  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 70
Default Second Sunday in the month of May?

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding 3rd (or 2nd) Sunday in a given year/month Bob Excel Worksheet Functions 5 March 31st 08 04:32 PM
Last day of month is saturday or sunday? Arne Hegefors Excel Programming 2 October 26th 07 10:56 AM
Timesheet Calculate Sunday times that changes every month jeromevw Excel Discussion (Misc queries) 2 May 13th 07 05:07 PM
HOW TO TRACK DATES OF A MONTH EXCEPT SUNDAY Akash Excel Programming 4 December 30th 06 11:17 AM
Date Command to Get the Sunday Before the First of the Month Minitman Excel Worksheet Functions 6 December 2nd 05 09:34 PM


All times are GMT +1. The time now is 08:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"