Finding 3rd (or 2nd) Sunday in a given year/month
 

For a given Year and Month, I need to find either the 3rd Sunday (if month =
Jan thru Oct) or the 2nd Sunday (if month = Nov or Dec).

Example:
Year = 2009
Month = 7
3rd Sunday in July 2009 = 7/19/2009

There are two additional rules that also need to be taken into account:
If the 1st of the month is Saturday, that weekend is counted as the 1st
weekend of the month. However, if the 1st of the month is Sunday, that
weekend is counted as the 5th weekend of the previous month.

Example:
Year = 2009
Month = 11
2nd Sunday in November 2009 = 11/15/2009

Any help in coming up with a formula that takes into account the
aforementioned 4 rules would be greatly appreciated.

Thanks,
Bob

Finding 3rd (or 2nd) Sunday in a given year/month
 

Hi Bob,

Look he

http://www.cpearson.com/excel/DateTi...tm#LastWeekday

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Bob" wrote in message ...
| For a given Year and Month, I need to find either the 3rd Sunday (if month =
| Jan thru Oct) or the 2nd Sunday (if month = Nov or Dec).
|
| Example:
| Year = 2009
| Month = 7
| 3rd Sunday in July 2009 = 7/19/2009
|
| There are two additional rules that also need to be taken into account:
| If the 1st of the month is Saturday, that weekend is counted as the 1st
| weekend of the month. However, if the 1st of the month is Sunday, that
| weekend is counted as the 5th weekend of the previous month.
|
| Example:
| Year = 2009
| Month = 11
| 2nd Sunday in November 2009 = 11/15/2009
|
| Any help in coming up with a formula that takes into account the
| aforementioned 4 rules would be greatly appreciated.
|
| Thanks,
| Bob
|

Finding 3rd (or 2nd) Sunday in a given year/month
 

On Mon, 31 Mar 2008 04:51:01 -0700, Bob wrote:

For a given Year and Month, I need to find either the 3rd Sunday (if month =
Jan thru Oct) or the 2nd Sunday (if month = Nov or Dec).

Example:
Year = 2009
Month = 7
3rd Sunday in July 2009 = 7/19/2009

There are two additional rules that also need to be taken into account:
If the 1st of the month is Saturday, that weekend is counted as the 1st
weekend of the month. However, if the 1st of the month is Sunday, that
weekend is counted as the 5th weekend of the previous month.

Example:
Year = 2009
Month = 11
2nd Sunday in November 2009 = 11/15/2009

Any help in coming up with a formula that takes into account the
aforementioned 4 rules would be greatly appreciated.

Thanks,
Bob

I believe this formula will do that, with a value in A1 that Excel recognizes
as a date:

=A1-DAY(A1)+15-WEEKDAY((A1-DAY(A1)))
+7*(MONTH(A1)<=10)+7*(WEEKDAY(A1-DAY(A1)+1)=1)
--ron

Finding 3rd (or 2nd) Sunday in a given year/month
 

Niek,
Thanks for the URL! I really appreciate it.

I also found a solution posted by Ron Rosenfeld which I modified as follows:

=IF(MONTH(A1)<11,A1-DAY(A1)+23-WEEKDAY(A1-DAY(A1)+1),A1-DAY(A1)+16-WEEKDAY(A1-DAY(A1)+1))

The above formula appears to take into account my 4 rules below, although I
have tested it with only the 12 months in 2009. I will continue to test it
with months in other years, just to be certain.

Thanks again for your help,
Bob


"Niek Otten" wrote:

Hi Bob,

Look he

http://www.cpearson.com/excel/DateTi...tm#LastWeekday

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Bob" wrote in message ...
| For a given Year and Month, I need to find either the 3rd Sunday (if month =
| Jan thru Oct) or the 2nd Sunday (if month = Nov or Dec).
|
| Example:
| Year = 2009
| Month = 7
| 3rd Sunday in July 2009 = 7/19/2009
|
| There are two additional rules that also need to be taken into account:
| If the 1st of the month is Saturday, that weekend is counted as the 1st
| weekend of the month. However, if the 1st of the month is Sunday, that
| weekend is counted as the 5th weekend of the previous month.
|
| Example:
| Year = 2009
| Month = 11
| 2nd Sunday in November 2009 = 11/15/2009
|
| Any help in coming up with a formula that takes into account the
| aforementioned 4 rules would be greatly appreciated.
|
| Thanks,
| Bob
|

Finding 3rd (or 2nd) Sunday in a given year/month
 

Ron,
I am constantly amazed with your sophisticated (and very efficient)
formulas. Thanks a million!!!
Bob



"Ron Rosenfeld" wrote:

On Mon, 31 Mar 2008 04:51:01 -0700, Bob wrote:

For a given Year and Month, I need to find either the 3rd Sunday (if month =
Jan thru Oct) or the 2nd Sunday (if month = Nov or Dec).

Example:
Year = 2009
Month = 7
3rd Sunday in July 2009 = 7/19/2009

There are two additional rules that also need to be taken into account:
If the 1st of the month is Saturday, that weekend is counted as the 1st
weekend of the month. However, if the 1st of the month is Sunday, that
weekend is counted as the 5th weekend of the previous month.

Example:
Year = 2009
Month = 11
2nd Sunday in November 2009 = 11/15/2009

Any help in coming up with a formula that takes into account the
aforementioned 4 rules would be greatly appreciated.

Thanks,
Bob

I believe this formula will do that, with a value in A1 that Excel recognizes
as a date:

=A1-DAY(A1)+15-WEEKDAY((A1-DAY(A1)))
+7*(MONTH(A1)<=10)+7*(WEEKDAY(A1-DAY(A1)+1)=1)
--ron

Finding 3rd (or 2nd) Sunday in a given year/month
 

On Mon, 31 Mar 2008 05:34:00 -0700, Bob wrote:

Ron,
I am constantly amazed with your sophisticated (and very efficient)
formulas. Thanks a million!!!
Bob

You're most welcome. Glad to help.
--ron