Listing only the workdays of a month
 

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.

Listing only the workdays of a month
 

Try

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

and

=WORKDAY(B1,1)

assuming the first is in B1

HTH

Bob

"Joe" wrote in message
...
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.

Listing only the workdays of a month
 

This would work better for the second formula

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

HTH

Bob

"Bob Phillips" wrote in message
...
Try

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

and

=WORKDAY(B1,1)

assuming the first is in B1

HTH

Bob

"Joe" wrote in message
...
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.

Listing only the workdays of a month
 

On Mon, 11 Jan 2010 01:46:01 -0800, Joe wrote:

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),Ho lidays),
"",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

Listing only the workdays of a month
 

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


"Joe" wrote in message
...
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.