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" <(E-Mail Removed)> wrote in message
news:82AD7C1C-0FC4-4DDD-A099-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:82AD7C1C-0FC4-4DDD-A099-(E-Mail Removed)...
>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 tools>add-ins and select analysis toolpak,
keep the installation cd handy since you will need it


--
Regards,

Peo Sjoblom


"John Krsulic" <(E-Mail Removed)> wrote in message
news:96C60FF7-02BB-45A0-B9EF-(E-Mail Removed)...
>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
>>
>>