Excel 2007 : extracting certain weekdays in a month

[rightsedfred]

Situation: as a language teacher, I travel to different companies on fixed days each month, for this I would like to generate a timesheet.

Problem: I am stuck with how to produce a list of, for example, Tuesdays and Thursdays in a given month. I understand how to produce weekdays, but can't see how to filter them. Ideally, my sheet would have a cell for the month and each weekday I teach, then a list of only those days would appear.

Any ideas?

[Haseeb Avarakkan]

Hello,

A1 enter the first day of the month, say 7/1/2001
B1, enter the day want to get, say Tue

A2,

=A1-WEEKDAY(A1-MATCH(B1,{"Sun";"Mon";"Tue";"Wed";"Thu";"Fri";"Sat"},0))+7

A3, copy down..

=A2+7

This will give only Tue after the start date. If you change B1 to Wed, would give only Wed after the start date.

If this is not you are looking please post a dummy file with expecting result.

[rightsedfred]

thx for your help, here is a file showing my expected result.

[Haseeb Avarakkan]

Here is the one way. On a seperate sheet get whole date in a selected month, then extract the day you want. In F2, you can enter days you want to display seperated by comma.

See the attached.

I am sure there should be more easier & elegant ways.

EDIT:

Without any helper cell or sheet, A6 copy down.

=IFERROR(INDEX(ROW(INDEX(A:A,(1&D$2&B$2)+0):INDEX(A:A,EOMONTH(1&D$2&B$2,0))),SMALL(IFERROR(IF(ISNUMBER(SEARCH(TEXT(ROW(INDEX(A:A,(1&D$2&B$2)+0):INDEX(A:A,EOMONTH(1&D$2&B$2,0))),"dddd"),F$2)),ROW($A$1:$A$31)),100),ROWS(A$6:A6))),"")

Array Formula, confirmed with Control+Shift+Enter rather than just Enter.

[Haseeb Avarakkan]

If you are on XL2010 you can use NETWORKDAYS.INTL & WORKDAY.INTL. Whichever days you want to show, Just enter a X in G2:G8.

See the attached