Finding every Wednesday and Saturday of every month

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

Hi,

Can someone please help me, I'm not that good at all on formulas?

I'm trying to find every Wednesday's and Saturday's date in every month.
I have the month displayed cell A2 (January 2004). What I would like is the
cell B1 to show the first Wednesday's date (example - 04) of this month, and
then in cell B2 to show the first Saturday's date (example - 07) of this
month. Then in cell B3 the second Wednesday's date (example - 11) and so
on.........

Is this possible in a formula?

Hope someone can help,
Thanks
Best regards,
Scott
 
Make sure the value in A2 is actually the first day of the
month (eg 1/1/04). Then format it as "January 04" if you
wish. In B1 insert:

=SMALL(IF(WEEKDAY(ROW(INDIRECT($A$2&":"&DATE(YEAR
($A$2),MONTH($A$2)+1,0))))={4,7},ROW(INDIRECT($A$2&":"&DATE
(YEAR($A$2),MONTH($A$2)+1,0)))),ROW())

and press ctrl/shift/enter. XL will {} around the formula
to signal that it's an array formula. Fill this formula
down to B10.

HTH
Jason
Atlanta, GA
 
In part you will need to use the following formula:

=TEXT(WEEKDAY(A2), "dddd")

This requires a date though. You would probably need a
table somewhere that provides the number of days in each
month, then using VBA and a loop cycle through each day X
times for a month. Where X is the number of days in that
month. I think I would format the field where you have the
Month as a date where the value actually entered is the
first day of the month. Set the format for the cell to
show only month and year.

I hope that helps!

Kevin
 
somewhere a table of this - I placed data in a3:c9
col1 col2 col3
1 2 5
2 1 4
3 0 3
4 6 2
5 5 1
6 4 0
7 3 6

this table represents the number of days to the first wed (col2) and
first sat (col3) for the 1st day being Monday (1) thru Sunday (7)

cell a14 1/1/2004

cell a15
=MIN(VLOOKUP(WEEKDAY($A$14,2),$A$3:$C$9,2),VLOOKUP(WEEKDAY($A$14,2),$A$3:$C$9,3))+A14

this gives first Wed or Sat whichever is first

cell a16
=MAX(VLOOKUP(WEEKDAY($A$14,2),$A$3:$C$9,2),VLOOKUP(WEEKDAY($A$14,2),$A$3:$C$9,3))+A14

this gives the first Wed or Sat whichever is second

cell a17 and following =a15+7
 
Back
Top