Formula to get 1st,2nd,3rd,4th,5th Sundays from month end date.
 

Need cell formula or UDF that will return the 1st, 2nd, 3rd, 4th, or 5th
Sunday's dates in cells when the month end date is given in a cell. For
example:

In cell P1, a date of 10/31/05 is entered. I need the following dates:

In cell F3 10/02/05 (the 1st Sunday in October)
In cell H3 10/09/05 (the 2nd Sunday in October)
In cell J3 10/16/05 (the 3rd Sunday in October)
In cell L3 10/23/05 (the 4th Sunday in October)
In cell N3 10/30/05 (the 5th Sunday in October)

Any formula suggestions would be greatly appreciated. Mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=471744

F3=DATE(YEAR(P1),MONTH(P1),1)+(7-WEEKDAY(DATE(YEAR(P1),MONTH(P1),1),2))
F4=F3+7
F5=F4+7
F6=F5+7
F7=F6+7

Depending on the number of weeks, the formula in F7 may result in a November
date. That can be fixed as well.

"mikeburg" wrote:


Need cell formula or UDF that will return the 1st, 2nd, 3rd, 4th, or 5th
Sunday's dates in cells when the month end date is given in a cell. For
example:

In cell P1, a date of 10/31/05 is entered. I need the following dates:

In cell F3 10/02/05 (the 1st Sunday in October)
In cell H3 10/09/05 (the 2nd Sunday in October)
In cell J3 10/16/05 (the 3rd Sunday in October)
In cell L3 10/23/05 (the 4th Sunday in October)
In cell N3 10/30/05 (the 5th Sunday in October)

Any formula suggestions would be greatly appreciated. Mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=471744

One way:

This is based on

http://cpearson.com/excel/DateTimeWS.htm#NthDoW

=DATE(YEAR(A1),MONTH(A1),1+((N-(D=WEEKDAY(
DATE(YEAR(A1),MONTH(A1),1))))*7)+(D-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))))

Where N = nth (e.g., for F3, 1; H3, 2)
D = Day of the week (Sunday = 1)
and A1 contains the last (or any other) day of the month.



In article ,
mikeburg
wrote:

Need cell formula or UDF that will return the 1st, 2nd, 3rd, 4th, or 5th
Sunday's dates in cells when the month end date is given in a cell. For
example:

In cell P1, a date of 10/31/05 is entered. I need the following dates:

In cell F3 10/02/05 (the 1st Sunday in October)
In cell H3 10/09/05 (the 2nd Sunday in October)
In cell J3 10/16/05 (the 3rd Sunday in October)
In cell L3 10/23/05 (the 4th Sunday in October)
In cell N3 10/30/05 (the 5th Sunday in October)

Any formula suggestions would be greatly appreciated. Mikeburg

Works perfectly. Used if statement on 5th Sunday to avoid going past
month end date!

You guys are wonderful. Would pay you a million dollars if I could
afford it! I am learning so very much from y'all.

Thanks again!

mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=471744

On Thu, 29 Sep 2005 08:50:48 -0500, mikeburg
wrote:


Need cell formula or UDF that will return the 1st, 2nd, 3rd, 4th, or 5th
Sunday's dates in cells when the month end date is given in a cell. For
example:

In cell P1, a date of 10/31/05 is entered. I need the following dates:

In cell F3 10/02/05 (the 1st Sunday in October)
In cell H3 10/09/05 (the 2nd Sunday in October)
In cell J3 10/16/05 (the 3rd Sunday in October)
In cell L3 10/23/05 (the 4th Sunday in October)
In cell N3 10/30/05 (the 5th Sunday in October)

Any formula suggestions would be greatly appreciated. Mikeburg

With any date in the month in P1, the first Monday is given by the formula:

=F3-DAY(F3)+8-WEEKDAY(F3-DAY(F3)+6).

So,

F3: =P1-DAY(P1)+8-WEEKDAY(P1-DAY(P1)+7)
H3: =F3+7
J3: =H3+7
I3: =J3+7
N3: =IF(MONTH(M3)=MONTH(M3+7),M3+7,"")

N3 will be blank if there are only four Sundays


--ron

On Thu, 29 Sep 2005 13:40:02 -0400, Ron Rosenfeld
wrote:

=F3-DAY(F3)+8-WEEKDAY(F3-DAY(F3)+6).

That should read:

=P1-DAY(P1)+8-WEEKDAY(P1-DAY(P1)+7)
--ron