Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
writing a formula for a colored value | New Users to Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |