networkdays
 

I have a problem for creating appointment schedule. For example,with a given
date No 1, say 24 Nov 06. The criteria is I cannot make appointment 5
working days before 24 Nov06 and 2 workdays after.
A2 = (date before); B2 = (Given Date No. 1); C3 = (Date after Given)
A2 B2 C2
=B2-5 B2+2
19.11.06 24.11.06 26.11.06

However,A2 - 19.11.06 fall on a sunday, the date should be 17.11.06 to
exclude saturday and sunday. Similar for C2 - 26.11.06 (Date for Appointment
should be after 28.11.06. Please help

networkdays
 

You need the WORKDAY function

=WORKDAY(B2,2) and =WORKDAY(B2,-5)

you can also exclude holidays if you wish

WORKDAY is part of Analysis ToolPak. If that isn't enabled use

Tools Add-ins tick "Analysis ToolPak" box

"ann chan" wrote:

I have a problem for creating appointment schedule. For example,with a given
date No 1, say 24 Nov 06. The criteria is I cannot make appointment 5
working days before 24 Nov06 and 2 workdays after.
A2 = (date before); B2 = (Given Date No. 1); C3 = (Date after Given)
A2 B2 C2
=B2-5 B2+2
19.11.06 24.11.06 26.11.06

However,A2 - 19.11.06 fall on a sunday, the date should be 17.11.06 to
exclude saturday and sunday. Similar for C2 - 26.11.06 (Date for Appointment
should be after 28.11.06. Please help

networkdays
 

Hi, it works. Thanks. Can you please enlighten me with the difference
between "workday" and "networkdays" function, and when do I apply these 2
functions. Thanks .

"daddylonglegs" wrote:

You need the WORKDAY function

=WORKDAY(B2,2) and =WORKDAY(B2,-5)

you can also exclude holidays if you wish

WORKDAY is part of Analysis ToolPak. If that isn't enabled use

Tools Add-ins tick "Analysis ToolPak" box

"ann chan" wrote:

I have a problem for creating appointment schedule. For example,with a given
date No 1, say 24 Nov 06. The criteria is I cannot make appointment 5
working days before 24 Nov06 and 2 workdays after.
A2 = (date before); B2 = (Given Date No. 1); C3 = (Date after Given)
A2 B2 C2
=B2-5 B2+2
19.11.06 24.11.06 26.11.06

However,A2 - 19.11.06 fall on a sunday, the date should be 17.11.06 to
exclude saturday and sunday. Similar for C2 - 26.11.06 (Date for Appointment
should be after 28.11.06. Please help

networkdays
 

hello ann, sorry I didn't see your reply until now....

perhaps you've worked it out by now but essentially NETWORKDAYS is used to
give the number of workdays between 2 known dates whereas WORKDAY returns a
date given the number of workdays to add to (or subtract from) a single date

"ann chan" wrote:

Hi, it works. Thanks. Can you please enlighten me with the difference
between "workday" and "networkdays" function, and when do I apply these 2
functions. Thanks .

"daddylonglegs" wrote:

You need the WORKDAY function

=WORKDAY(B2,2) and =WORKDAY(B2,-5)

you can also exclude holidays if you wish

WORKDAY is part of Analysis ToolPak. If that isn't enabled use

Tools Add-ins tick "Analysis ToolPak" box

"ann chan" wrote:

I have a problem for creating appointment schedule. For example,with a given
date No 1, say 24 Nov 06. The criteria is I cannot make appointment 5
working days before 24 Nov06 and 2 workdays after.
A2 = (date before); B2 = (Given Date No. 1); C3 = (Date after Given)
A2 B2 C2
=B2-5 B2+2
19.11.06 24.11.06 26.11.06

However,A2 - 19.11.06 fall on a sunday, the date should be 17.11.06 to
exclude saturday and sunday. Similar for C2 - 26.11.06 (Date for Appointment
should be after 28.11.06. Please help

networkdays
 

Hi daddylonglegs,

Thanks for your info. As I am quite new, it is really of great help.
I do have another question to ask - when I import text file using a macro
using fixed width, I observed that there is a line of saying :

Fieldinfo:=Array(Array(0,1).Array(24,1), Array(36,9), Array(37,1)...
TrailingMinusNumbers:=True

Please enlighten me on what Array(0,1), Array(24,1) are? Is it the row and
column?
and I cannot run this macro from an older Excel version.

How can I export this worksheet - say name "XXXX" to Access use vba in the
same macro.

Thanks

Ann





"daddylonglegs" wrote:

hello ann, sorry I didn't see your reply until now....

perhaps you've worked it out by now but essentially NETWORKDAYS is used to
give the number of workdays between 2 known dates whereas WORKDAY returns a
date given the number of workdays to add to (or subtract from) a single date

"ann chan" wrote:

Hi, it works. Thanks. Can you please enlighten me with the difference
between "workday" and "networkdays" function, and when do I apply these 2
functions. Thanks .

"daddylonglegs" wrote:

You need the WORKDAY function

=WORKDAY(B2,2) and =WORKDAY(B2,-5)

you can also exclude holidays if you wish

WORKDAY is part of Analysis ToolPak. If that isn't enabled use

Tools Add-ins tick "Analysis ToolPak" box

"ann chan" wrote:

I have a problem for creating appointment schedule. For example,with a given
date No 1, say 24 Nov 06. The criteria is I cannot make appointment 5
working days before 24 Nov06 and 2 workdays after.
A2 = (date before); B2 = (Given Date No. 1); C3 = (Date after Given)
A2 B2 C2
=B2-5 B2+2
19.11.06 24.11.06 26.11.06

However,A2 - 19.11.06 fall on a sunday, the date should be 17.11.06 to
exclude saturday and sunday. Similar for C2 - 26.11.06 (Date for Appointment
should be after 28.11.06. Please help