I need to create a sheet where the days of the month show accross the top of
the columns. I have been starting with the first day of the month and auto
filling to the last day and then manually going in and deleting out the
weekends. Is there a way to create a formula where I can auto fill but not
have it include the weekends.

My heading should be July 2 July 3 July 4 July 5 July 6 July 9

Enter date in A1
In B1: =WORKDAY(A1,1)
copy across


"John Krsulic" wrote:

I need to create a sheet where the days of the month show accross the top of
the columns. I have been starting with the first day of the month and auto
filling to the last day and then manually going in and deleting out the
weekends. Is there a way to create a formula where I can auto fill but not
have it include the weekends.

My heading should be July 2 July 3 July 4 July 5 July 6 July 9

put the date of the last day of the previous month in A1, and in B1 enter

=IF(WEEKDAY(A1)=7,2,1)+A1

in C1 enter

=IF(MONTH(B1+1)<MONTH(B1),"",IF(WEEKDAY(B1)=7,2,1 )+B1)

and copy across

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"John Krsulic" wrote in message
...
I need to create a sheet where the days of the month show accross the top
of
the columns. I have been starting with the first day of the month and auto
filling to the last day and then manually going in and deleting out the
weekends. Is there a way to create a formula where I can auto fill but not
have it include the weekends.

My heading should be July 2 July 3 July 4 July 5 July 6 July 9

oops, C1 and across should be

=IF(MONTH(B1+1)<MONTH(B1),"",IF(WEEKDAY(B1)=6,3,1 )+B1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"John Krsulic" wrote in message
...
I need to create a sheet where the days of the month show accross the top
of
the columns. I have been starting with the first day of the month and auto
filling to the last day and then manually going in and deleting out the
weekends. Is there a way to create a formula where I can auto fill but not
have it include the weekends.

My heading should be July 2 July 3 July 4 July 5 July 6 July 9

I need to create a sheet where the days of the month show accross the top
of
the columns. I have been starting with the first day of the month and auto
filling to the last day and then manually going in and deleting out the
weekends. Is there a way to create a formula where I can auto fill but not
have it include the weekends.

My heading should be July 2 July 3 July 4 July 5 July 6 July 9

Teethless mama's solution is the tightest method (once you format it for the
display you want), but if you don't have (or don't want to install) the
Analysis ToolPak, you can do this...

A1: =TEXT(DATE(2007,7,2),"mmmm d")

B1 and up
====================
=TEXT($A$1+MOD(COLUMN(B1)-1,5)+7*INT((COLUMN(B1)-1)/5),"mmmm d")

Rick

I tried Toothless Mama's formula and I get a name error.
In cell A1 I typed in todays date 7/2 Excel converted it to 2 Jul (fine with
me) in cell B1 I typed the workday formula. In cell B1 I get the name error.

What is the Analysis tool pack? What does it do for me and where do I get it?
"Rick Rothstein (MVP - VB)" wrote:

I need to create a sheet where the days of the month show accross the top
of
the columns. I have been starting with the first day of the month and auto
filling to the last day and then manually going in and deleting out the
weekends. Is there a way to create a formula where I can auto fill but not
have it include the weekends.

My heading should be July 2 July 3 July 4 July 5 July 6 July 9

Teethless mama's solution is the tightest method (once you format it for the
display you want), but if you don't have (or don't want to install) the
Analysis ToolPak, you can do this...

A1: =TEXT(DATE(2007,7,2),"mmmm d")

B1 and up
====================
=TEXT($A$1+MOD(COLUMN(B1)-1,5)+7*INT((COLUMN(B1)-1)/5),"mmmm d")

Rick

It comes with excel/office, do toolsadd-ins and select analysis toolpak,
keep the installation cd handy since you will need it


--
Regards,

Peo Sjoblom


"John Krsulic" wrote in message
...
I tried Toothless Mama's formula and I get a name error.
In cell A1 I typed in todays date 7/2 Excel converted it to 2 Jul (fine
with
me) in cell B1 I typed the workday formula. In cell B1 I get the name
error.

What is the Analysis tool pack? What does it do for me and where do I get
it?
"Rick Rothstein (MVP - VB)" wrote:

I need to create a sheet where the days of the month show accross the
top
of
the columns. I have been starting with the first day of the month and
auto
filling to the last day and then manually going in and deleting out the
weekends. Is there a way to create a formula where I can auto fill but
not
have it include the weekends.

My heading should be July 2 July 3 July 4 July 5 July 6 July 9

Teethless mama's solution is the tightest method (once you format it for
the
display you want), but if you don't have (or don't want to install) the
Analysis ToolPak, you can do this...

A1: =TEXT(DATE(2007,7,2),"mmmm d")

B1 and up
====================
=TEXT($A$1+MOD(COLUMN(B1)-1,5)+7*INT((COLUMN(B1)-1)/5),"mmmm d")

Rick