Business Days Only
 

I start new spreadsheets on Jan 1st and July 1st, then click and drag
them out row "A" to have 6 months of days to work with. Since I only
want business days, I always have to manually go back and delete all of
the Saturdays and Sundays off each sheet. Not the hardest work in the
world but since I'm lazy I'd like to know if there is a way that I can
let Excel 2000 delete them automatically. All I need is Monday thru
Friday anyway.


--
lsmft
------------------------------------------------------------------------
lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=520746

Business Days Only
 

Hi

Use
= WORKDAY(A2,1)
in A3 and fill down

Andy.

"lsmft" wrote in message
...

I start new spreadsheets on Jan 1st and July 1st, then click and drag
them out row "A" to have 6 months of days to work with. Since I only
want business days, I always have to manually go back and delete all of
the Saturdays and Sundays off each sheet. Not the hardest work in the
world but since I'm lazy I'd like to know if there is a way that I can
let Excel 2000 delete them automatically. All I need is Monday thru
Friday anyway.


--
lsmft
------------------------------------------------------------------------
lsmft's Profile:
http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=520746

Business Days Only
 

Thanks for the fast response Andy, but this is not working for me.
I mistated my question though. I should have stated that I click on
cell A-1 and drag across on Row 1 until I get to June 30. In this way
each day will have it's own column.
Sorry for my confusing question.
Let's say:
A-1 is Jan 1, 2006
B-1 is Jan 2, 2006
C-1 is Jan 3, 2006 etc.


--
lsmft
------------------------------------------------------------------------
lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=520746

Business Days Only
 

Thanks for the fast response Andy, but this is not working for me.
I mistated my question though. I should have stated that I click on
cell A-1 and drag across on Row 1 until I get to June 30. In this way
each day will have it's own column.
Sorry for my confusing question.
Let's say:
A-1 is Jan 1, 2006
B-1 is Jan 2, 2006
C-1 is Jan 3, 2006 etc.


--
lsmft
------------------------------------------------------------------------
lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=520746

Business Days Only
 

You can do exactly the same, but the other way around!
In B1 put
=WORKDAY(A1,1)
and fill it across!

Andy.

"lsmft" wrote in
message ...

Thanks for the fast response Andy, but this is not working for me.
I mistated my question though. I should have stated that I click on
cell A-1 and drag across on Row 1 until I get to June 30. In this way
each day will have it's own column.
Sorry for my confusing question.
Let's say:
A-1 is Jan 1, 2006
B-1 is Jan 2, 2006
C-1 is Jan 3, 2006 etc.


--
lsmft
------------------------------------------------------------------------
lsmft's Profile:
http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=520746

Business Days Only
 

Andy,
In cell A1 I put 01/01/06
In cell B1 I put =WORDAY(A1,1)
All I'm getting is #NAME?
When I click and drag, I drag #NAME? horizontally across row 1.
Am I doing something wrong here?


--
lsmft
------------------------------------------------------------------------
lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=520746

Business Days Only
 

On Thu, 9 Mar 2006 15:19:33 -0600, lsmft
wrote:


Andy,
In cell A1 I put 01/01/06
In cell B1 I put =WORDAY(A1,1)
All I'm getting is #NAME?
When I click and drag, I drag #NAME? horizontally across row 1.
Am I doing something wrong here?

You need to install the Analysis ToolPak.

See HELP for the WORKDAY function and it will tell you what to do when you see
a #NAME error
--ron

Business Days Only
 

Try WORKDAY instead of WORDAY!

Andy.

"lsmft" wrote in
message ...

Andy,
In cell A1 I put 01/01/06
In cell B1 I put =WORDAY(A1,1)
All I'm getting is #NAME?
When I click and drag, I drag #NAME? horizontally across row 1.
Am I doing something wrong here?


--
lsmft
------------------------------------------------------------------------
lsmft's Profile:
http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=520746

Business Days Only
 

Andy,
Sorry for the misspelling.
However I did have it spelled correctly in the Excel cell.
I'm now going to see what I can find about analysis tool pack.


--
lsmft
------------------------------------------------------------------------
lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=520746

Business Days Only
 

"Andy" wrote:
Hi

Use
= WORKDAY(A2,1)
in A3 and fill down

Won't that only tell you which are the saturdays/sundays?

I use.
A1 = 01/01/06
A2 = if(workday(A1,2)=5,A1+3,A1+1)

Which when dragged across will give you ony Monday - Friday's without the
need to delete anything.

Business Days Only
 

On Fri, 10 Mar 2006 03:16:27 -0800, PaulW
wrote:

"Andy" wrote:
Hi

Use
= WORKDAY(A2,1)
in A3 and fill down

Won't that only tell you which are the saturdays/sundays?

I use.
A1 = 01/01/06
A2 = if(workday(A1,2)=5,A1+3,A1+1)

Which when dragged across will give you ony Monday - Friday's without the
need to delete anything.

You might want to consider the difference between the functions WORKDAY and
WEEKDAY.

As written, the conditional portion of your equation in A2 (and as copied
down), will ALWAYS evaluate to FALSE.


--ron

Business Days Only
 

PaulW Wrote:
"Andy" wrote:
Hi

Use
= WORKDAY(A2,1)
in A3 and fill down

Won't that only tell you which are the saturdays/sundays?

I use.
A1 = 01/01/06
A2 = if(workday(A1,2)=5,A1+3,A1+1)

Which when dragged across will give you ony Monday - Friday's without
the
need to delete anything.

As Ron says, that won't work as it stands. If you don't have access to
Analysis ToolPak you could use

= if(weekday(A1)=6,A1+3,A1+1)

but that only works if A1 is a weekday (which 01/01/2006 isn't) - and
it doesn't exclude holidays, although with a more complex formula you
could incorporate both


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=520746