Return the Date of a certain Day Each Month

[Matt.Russett]

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!

 

[Guest]

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

 

[Ron Rosenfeld]

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