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