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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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


Kelly said:
I am trying to determine if there is a formula or Autofill (with a custom
Click to expand...
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!
 
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
 
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

Kelly said:
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
Click to expand...
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?
 
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

Kelly said:
Hi Vasant
Click to expand...
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
 
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.
 
Back
Top