Formula or Autofill to show first and third Tuesdays (for example) of each month

[Kelly]

I am trying to determine if there is a formula or Autofill (with a custom series) to show the first and third Tuesdays (or second and fourth Wednesdays, whatever) for every month to quickly set up a meeting schedule that runs through the year. Thanks!

 

[Vasant Nanavati]

Somewhat kludgy, but:

For the first Tuesday of the year 2004:

=DATE(2004,1,1)+IF(WEEKDAY(DATE(2004,1,1))<>3,MOD(3-WEEKDAY(DATE(2004,1,1)),
7))

If the above is in cell A1, enter in cell A2:

=B1+14+IF(MONTH(B1+14)=MONTH(B1-14),7)

Copy down.

Similar logic can be used for the second and fourth Wednesdays.

--

Vasant

I am trying to determine if there is a formula or Autofill (with a custom

series) to show the first and third Tuesdays (or second and fourth
Wednesdays, whatever) for every month to quickly set up a meeting schedule
that runs through the year. Thanks!

 

[Vasant Nanavati]

=B1+14+IF(MONTH(B1+14)=MONTH(B1-14),7)<<

Sorry; that should be A1, not B1.

 

[Kelly]

Hi Vasant,

Thank you! I really appreciate your time on this. I have one more question, please

I tried your formula and it works great for the first and third Tuesdays of each month. But I'm not sure if I fully understand the formula enough to be able to convert it to, say, the second and fourth Wednesdays of each month. Could you show me how that would look

Thanks very much
Kell

----- Vasant Nanavati wrote: ----

Sorry; that should be A1, not B1

 

[Vasant Nanavati]

I think this should work but you may want to test it:

For cell A1:

=DATE(2004,1,8)+IF(WEEKDAY(DATE(2004,1,8))<>4,MOD(4-WEEKDAY(DATE(2004,1,8)),
7))

For cell A2 and to be copied down:

=A1+14+IF(DAY(A1+14)<8,7)

--

Vasant

Hi Vasant,

Thank you! I really appreciate your time on this. I have one more question, please:

I tried your formula and it works great for the first and third Tuesdays

of each month. But I'm not sure if I fully understand the formula enough to
be able to convert it to, say, the second and fourth Wednesdays of each
month. Could you show me how that would look?

 

[Kelly]

Hi Vasant -

Again, thanks for your wonderful help. I really appreciate your time and expertise!

Kell




----- Vasant Nanavati wrote: ----

I think this should work but you may want to test it

For cell A1

=DATE(2004,1,8)+IF(WEEKDAY(DATE(2004,1,8))<>4,MOD(4-WEEKDAY(DATE(2004,1,8))
7)

For cell A2 and to be copied down

=A1+14+IF(DAY(A1+14)<8,7

--

Vasan

Hi Vasant

of each month. But I'm not sure if I fully understand the formula enough t
be able to convert it to, say, the second and fourth Wednesdays of eac
month. Could you show me how that would look

 

[Vasant Nanavati]

You're most welcome!

Once you have the dates set up as you want, you may want to convert them
from formulas to hard numbers (do a Copy | Paste Special | Values over them)
so that they won't change if rows are inserted or other changes made
affecting the formula references.