Auto Fill days of the month (not including weekends)

[John Krsulic]

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]

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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[Rick Rothstein \(MVP - VB\)]

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

 

[John Krsulic]

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?

 

[Peo Sjoblom]

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