Determining the Sunday date of the third "full" weekend of a month

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

For a given month number (in column A) and year (in column B), I need to
determine the Sunday date of the third "full" weekend. A "full" weekend is
defined as one in which a Saturday and Sunday occur within the same month
(i.e., Saturday and Sunday do NOT straddle two months).

For example, the Sunday date of the third "full" weekend in November 2009 is
November 22, whereas the Sunday date of the third "full" weekend in December
2009 is December 20.

I would greatly appreciate any help in coming up with the formula (and
worksheet function) that will accurately and consistently determine the
Sunday date of the third "full" weekend of a month.

Thanks,
Bob
 
Give this formula a try...

=DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,7))+7*(DAY(DATE(B1,A1,8)-WEEKDAY(DATE(B1,A1,7)))=1)
 
Another one (a bit shorter)

=DATE(B1,A1,22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))+1

If this post helps click Yes
 
Oops.. correction..
=DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,1))+1


If this post helps click Yes
 
Here is a shorter version...

=DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,7))+7*(WEEKDAY(DATE(B1,A1,1))=1)
 
=DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,1))+1

For Feb 2009 that formula returns 2/22/2009 which is the 4th Sunday of the
month. The 3rd weekday of a month will always be <=21st of the month.

Try this:

=DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,7))

The general formula for the nth weekday of a month is:

DATE(year,month,1+n*7)-WEEKDAY(DATE(year,month,8-dow))

Where:

n = nth weekday = a number from 1 to 5 (for the 3rd Sunday n = 3)
dow = a number from 1 to 7, Sunday =1 through Saturday = 7
 
Biff; the OP is looking for a "full" weekend which the OP has defined as one
in which a Saturday and Sunday occur within the same month..

If this post helps click Yes
 
Jacob's formula is correct... the OP asked for the Sunday date for the 3rd
FULL weekend... both Saturday and Sunday being within the current month.
February 1, 2009 occurs on a Sunday which means its companion Saturday is in
the previous month; hence, that first Sunday is not part of a full weekend
and is not counted.
 
I was under the impression the OP wanted the date for the 3rd full Sunday of
the month, not the day number.
 
I was under the impression the OP wanted the date for the 3rd full Sundayof
the month, not the day number.
Click to expand...
OOOPPPSSS - you're quite right <g>

Mine should be . . .

=DATE(B13,A13,23-WEEKDAY(DATE(B13,A13,1),1))

Much the same as Jacob's

Alan Lloyd
 
For a given month number (in column A) and year (in column B), I need to
determine the Sunday date of the third "full" weekend. A "full" weekend is
defined as one in which a Saturday and Sunday occur within the same month
(i.e., Saturday and Sunday do NOT straddle two months).

For example, the Sunday date of the third "full" weekend in November 2009 is
November 22, whereas the Sunday date of the third "full" weekend in December
2009 is December 20.

I would greatly appreciate any help in coming up with the formula (and
worksheet function) that will accurately and consistently determine the
Sunday date of the third "full" weekend of a month.

Thanks,
Bob
Click to expand...

With some date in the month of interest in A1:

=A1-DAY(A1)+23-WEEKDAY(A1-DAY(A1)+1)

--ron
 
For a given month number (in column A) and year (in column B), I need to
determine the Sunday date of the third "full" weekend. A "full" weekend is
defined as one in which a Saturday and Sunday occur within the same month
(i.e., Saturday and Sunday do NOT straddle two months).

For example, the Sunday date of the third "full" weekend in November 2009 is
November 22, whereas the Sunday date of the third "full" weekend in December
2009 is December 20.

I would greatly appreciate any help in coming up with the formula (and
worksheet function) that will accurately and consistently determine the
Sunday date of the third "full" weekend of a month.

Thanks,
Bob
Click to expand...

If you must have the month number in A1 and the year in B1, then:

=DATE(B1,A1,23)-WEEKDAY(DATE(B1,A1,1))

--ron
 
Wow! What a great formula!

While I understand how the FLOOR function works in general, I can't seem to
figure out how it works when used with a date, as in your formula.

Thanks for your help.

Regards,
Bob
 
Back
Top