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

[mikeburg]

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

 

[Guest]

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.

 

[JE McGimpsey]

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.

 

[mikeburg]

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

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

Thanks again!

mikebur

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

That should read:

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