Thread: Populate a field with just workdays

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

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(IND IRECT("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


"Arlen" <Arlen@discussions.microsoft.com> wrote in message
news:5C39860F-FC93-41F8-9D5C-0DE531662163@microsoft.com...
>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

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(IND IRECT("1:31"))))))
(array enterered)

"N Harkawat" <nh@nh.com> wrote in message
news:uO$fPIp8EHA.208@TK2MSFTNGP12.phx.gbl...
> 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(IND IRECT("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
>
>
> "Arlen" <Arlen@discussions.microsoft.com> wrote in message
> news:5C39860F-FC93-41F8-9D5C-0DE531662163@microsoft.com...
>>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
>
>

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.