Listing only the workdays of a month

[Joe]

I want a formula that can check on a date such as 1/1/2010 and return all the
work days for January for example.

I used:
=WORKDAY(DATE(YEAR($A$1),MONTH($A$1)+1,1),-21)
and then in the cell below it:
=WORKDAY(DATE(YEAR($A$1),MONTH($A$1)+1,1),-20)
and on down to get all the days listed.

However I need some kind of check on the month I am pulling the date from so
it does not go over into the previous or following month depending on how
many days in a month.

 

[Bob Phillips]

Try

=WORKDAY(DATE(YEAR($A$1),MONTH($A$1),1)-1,1)

and

=WORKDAY(B1,1)

assuming the first is in B1

HTH

Bob

 

[Bob Phillips]

This would work better for the second formula

=IF(B1="","",IF(MONTH(WORKDAY(B1,1))=MONTH(B1),WORKDAY(B1,1),""))

HTH

Bob

 

[Ron Rosenfeld]

I want a formula that can check on a date such as 1/1/2010 and return all the
work days for January for example.

I used:
=WORKDAY(DATE(YEAR($A$1),MONTH($A$1)+1,1),-21)
and then in the cell below it:
=WORKDAY(DATE(YEAR($A$1),MONTH($A$1)+1,1),-20)
and on down to get all the days listed.

However I need some kind of check on the month I am pulling the date from so
it does not go over into the previous or following month depending on how
many days in a month.

Try this:

A1: The *first* of the month
A2:

=IF(ROWS($1:1)>NETWORKDAYS($A$1,EOMONTH($A$1,0),Holidays),
"",WORKDAY($A$1-1,ROWS($1:1),Holidays))

and fill down as far as you want, at least as far as the maximum number of
workdays in any month.

The EOMONTH function requires the Analysis ToolPak be installed for versions of
Excel prior to 2007. (See HELP for the function for how to do this).

If this is not possible, and if you wanted to have ANY date in the month in A1,
then you could use this:

=IF(ROWS($1:1)>NETWORKDAYS($A$1-DAY($A$1)+1,DATE(YEAR($A$1-DAY($A$1)+1),
MONTH($A$1-DAY($A$1)+1)+1,0),Holidays),"",WORKDAY($A$1-DAY($A$1)+1-1,ROWS($1:1),Holidays))

--ron

 

[Arvi Laanemets]

Hi

When the list starts from row1 and continues down, enter into 1st cell of
some column the formula:
=WORKDAY($A$1-1,ROW(),HolydayList)
, and copy it down for as much rows as you need.

Or use formula:
=IF(MONTH(WORKDAY($A$1-1,ROW(),HolydayList))=MONTH($A$1+1),WORKDAY($A$1-1,ROW(),HolydayList),"")
instead - so long you have the formula copied down enough once, you don't
have to bother about how much down you have to copy formulas later.

HolydayList - a range reference (or a named range), where all state holydays
for timespan used in calculations are stored.


Arvi Laanemets