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