Question: How do i find different weekdays in a date range?

Answer:

The formula in cell A5:G100:

=IF(ISERROR(SMALL(IF(WEEKDAY($B$1+(ROW( INDIRECT("1:"&($B$2-$B$1+1)))-1))=COLUMN();$B$1+(ROW(INDIRECT("1:"&($B$2-$B$1+1)))-1);"");ROW()-4));""; SMALL(IF(WEEKDAY($B$1+(ROW(INDIRECT("1:"&($B$2-$B$1+1)))-1))=COLUMN();$B$1+(ROW( INDIRECT("1:"&($B$2-$B$1+1)))-1);"");ROW()-4))

What does this formula do? Let us start with this part:
WEEKDAY($B$1+(ROW(INDIRECT("1:"&($B$2-$B$1+1)))-1))=COLUMN()
It creates an array with the start of the startdate ($B$1) andchecks if it equals the column number. The column number is in this case the same thing as the weekday number. Sunday=1, column() = 1.

So if the weekday number is the same as the column number the formula will return the corresponding date.
IF(WEEKDAY($B$1+(ROW(INDIRECT("1:"&($B$2-$B$1+1)))-1))=COLUMN();$B$1+(ROW( INDIRECT("1:"&($B$2-$B$1+1)))-1);"")

The next part sorts the array from small to large.
SMALL(IF(WEEKDAY($B$1+(ROW(INDIRECT("1:"&($B$2-$B$1+1)))-1))=COLUMN();$B$1+(ROW( INDIRECT("1:"&($B$2-$B$1+1)))-1);"");ROW()-4))

The last part of the formula removes any errors.

Download excel sample file for this tutorial. Yellow cells are editable by the user.
find-weekday-in-period.xls
(Excel 97-2003 Workbook *.xls)

WEEKDAY(serialnumber;[return_type])
Returns a number from 1 to 7 identifing the day of the week of a date

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

COLUMN(reference)
returns the column number of a reference

INDIRECT(ref_text;[a1])
Returns the reference specified by a text string