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

Even shorter

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

Alan Lloyd

I was under the impression the OP wanted the date for the 3rd full Sunday of
the month, not the day number.

--
Rick (MVP - Excel)


wrote in message
...
Even shorter

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

Alan Lloyd

On Sep 8, 8:52�am, "Rick Rothstein"
wrote:
I was under the impression the OP wanted the date for the 3rd full Sunday of
the month, not the day number.

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

Rick,
Thanks for your help. I really appreciate it.
Bob


"Rick Rothstein" wrote:

Here is a shorter version...

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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)

--
Rick (MVP - Excel)


"Bob" wrote in message
...
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

Jacob,
Thanks for your elegant and concise formula!
Bob


"Jacob Skaria" wrote:

Oops.. correction..
=DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,1))+1


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Another one (a bit shorter)

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

If this post helps click Yes
---------------
Jacob Skaria


"Bob" wrote:

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

Hello,

I suggest
=FLOOR(DATE(B1,A1,21),7)+1

Regards,
Bernd

On Mon, 7 Sep 2009 20:24:01 -0700, Bob wrote:

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

With some date in the month of interest in A1:

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

--ron

On Mon, 7 Sep 2009 20:24:01 -0700, Bob wrote:

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

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

Ron,
Thanks for your help. I greatly appreciate it.
Bob


"Ron Rosenfeld" wrote:

On Mon, 7 Sep 2009 20:24:01 -0700, Bob wrote:

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

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


"Bernd P" wrote:

Hello,

I suggest
=FLOOR(DATE(B1,A1,21),7)+1

Regards,
Bernd