Populate a field with just workdays

[Guest]

I am creating a spreadsheet to keep track of dentist office productivity. In
one box, I want to allow the user to enter the month and have the workdates
change accordingly. For example, our office works Mondays through Thursdays.
In 2005, the first Monday in January is the 3rd. How could I change the
dates to read 3, 4, 5, 6, then skip to 10, 11, 12, 13, then skip until all
the M-Th of the month are included.

These dates should change if the user types in a different month.

Thank you for your help.

Arlen

 

[N Harkawat]

Have the user enter any date of the desired month in cell A1 say
"01/01/2005"

then on cell A3 enter the following formula (array enter it by pressing
ctrl+shift+enter)
=MIN(IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1),ROW(INDIRECT("1:31"))),3)<5,(DATE(YEAR(A1),MONTH(A1),ROW(INDIRECT("1:31"))))))

and copy this formula from cell a4 thru say a30
=IF(A3="","",IF(MONTH(A3+IF(WEEKDAY(A3+1,3)<4,1,4))=MONTH($A$1),(A3+IF(WEEKDAY(A3+1,3)<4,1,4)),""))

this will produce a list of work days for that month that are from monday
thru thursday

 

[N Harkawat]

My mistake on cell a3 enter the following instead
=MIN(IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1),ROW(INDIRECT("1:31"))),3)<4,(DATE(YEAR(A1),MONTH(A1),ROW(INDIRECT("1:31"))))))
(array enterered)

 

[hrlngrv]

Arlen wrote...

I am creating a spreadsheet to keep track of dentist office productivity. In
one box, I want to allow the user to enter the month and have the workdates
change accordingly. For example, our office works Mondays through Thursdays.
In 2005, the first Monday in January is the 3rd. How could I change the
dates to read 3, 4, 5, 6, then skip to 10, 11, 12, 13, then skip until all
the M-Th of the month are included.

These dates should change if the user types in a different month.

If the user entered the month alone in cell A1, and that entry was
always assumed to be in the current year, then the first weekday of
that month would be given by

=("1-"&A1)+(WEEKDAY("1-"&A1,3)>3)*(7-WEEKDAY("1-"&A1,3))

If that formula were in cell A3, then the next workday would be given
by

=IF(COUNT(1/(DAY(A3)<DAY(A3+IF(WEEKDAY(A3,3)<3,1,4)))),
A3+IF(WEEKDAY(A3,3)<3,1,4),"")

Fill this down into A4:A21. Give these cells the custom number format d.