Business Days Only

L

lsmft

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.
 
L

lsmft

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.
 
L

lsmft

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.
 
G

Guest

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

Andy.
 
L

lsmft

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?
 
R

Ron Rosenfeld

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?
Click to expand...

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
 
L

lsmft

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.
 
P

PaulW

Andy said:
Hi

Use
= WORKDAY(A2,1)
in A3 and fill down
Click to expand...

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.
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
D

daddylonglegs

PaulW said:
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.
Click to expand...

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
 
Top