Return the Date of a certain Day Each Month
I have data that needs to be updated in a system on certain days in a
month as required by contract. Here is an example of something that I am currently doing that is similar to my question I have something that needs to be updated every Tuesday In cell A1 there is the =Today() function. Today is Wednesday 2/28/07 Cell B2 "Current Weekday" = Weekday(A1) = 4 Cell C2 "Desired Weekday" = 3 (for Tuesday) Cell D2 "Coefficient" = C2-B2 = -1 So.. the Update date would be A1+D2 = (2/28/07 + -1) = 2/27/07 (Tuesday of current week) Now my question is what if I have something that needs to be updated on the Second Monday of every month? How can I set up a formula that will return the Date of the 2nd Monday for the current month (based on =Today()) Thank you! |
Return the Date of a certain Day Each Month
In B1 enter the year:
2007 In B2 enter the month formula: =MONTH(TODAY()) In B3 enter which (1 for the first): 2 In B4 enter the daynumber (1 for Sunday) 2 So 2,2 in B3:B4 means the second Monday then: =DATE(B1,B2,1+((B3-(B4=WEEKDAY(DATE(B1,B2,1))))*7)+(B4-WEEKDAY(DATE(B1,B2,1)))) will display: 2/12/2007 -- Gary's Student gsnu200708 "Matt.Russett" wrote: I have data that needs to be updated in a system on certain days in a month as required by contract. Here is an example of something that I am currently doing that is similar to my question I have something that needs to be updated every Tuesday In cell A1 there is the =Today() function. Today is Wednesday 2/28/07 Cell B2 "Current Weekday" = Weekday(A1) = 4 Cell C2 "Desired Weekday" = 3 (for Tuesday) Cell D2 "Coefficient" = C2-B2 = -1 So.. the Update date would be A1+D2 = (2/28/07 + -1) = 2/27/07 (Tuesday of current week) Now my question is what if I have something that needs to be updated on the Second Monday of every month? How can I set up a formula that will return the Date of the 2nd Monday for the current month (based on =Today()) Thank you! |
Return the Date of a certain Day Each Month
On 28 Feb 2007 09:57:14 -0800, "Matt.Russett" wrote:
I have data that needs to be updated in a system on certain days in a month as required by contract. Here is an example of something that I am currently doing that is similar to my question I have something that needs to be updated every Tuesday In cell A1 there is the =Today() function. Today is Wednesday 2/28/07 Cell B2 "Current Weekday" = Weekday(A1) = 4 Cell C2 "Desired Weekday" = 3 (for Tuesday) Cell D2 "Coefficient" = C2-B2 = -1 So.. the Update date would be A1+D2 = (2/28/07 + -1) = 2/27/07 (Tuesday of current week) Now my question is what if I have something that needs to be updated on the Second Monday of every month? How can I set up a formula that will return the Date of the 2nd Monday for the current month (based on =Today()) Thank you! In general, the 2nd NDay of the month would be given by the formula: =A1+15-DAY(A1)-WEEKDAY(A1-DAY(A1)+8-DOW) Where A1 is some date in the month of concern, and DOW is day of the week (Sun=1, Mon=2, ...) So for your example, 2nd Monday of the current month: =TODAY()+15-DAY(TODAY())-WEEKDAY(TODAY()-DAY(TODAY())+6) --ron |
All times are GMT +1. The time now is 03:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com