Can anyone help me on this? I'm trying to figure out the number of days
that it takes to do a project excluding weekends, but I keep getting
NAME errors whenever I try to use the formula that Excel Help has for
Workday and don't know if there is another formula to use to get the
info I want... any help on this would be appreciated!!! So far I've
been able to compute the number of days including weekends by
subtracting my start date from my end date, but that number doesn't
really give me what I need, as I want to know how many WORKDAYS it
takes. The Workday formula is based off a start date plus however many
workdays, so that doesn't help because that is the info I am lacking.
HELP?!

-VB

....wanna de-stress? try a stress ball from Office Playground!!! :)


--
myplaygroundismyoffice
------------------------------------------------------------------------
myplaygroundismyoffice's Profile: http://www.msusenet.com/member.php?userid=2247
View this thread: http://www.msusenet.com/t-1870545398

Hi

To use WORKDAYS function, you must have Analysis Toolpack Ad-In installed .


Arvi Laanemets


"myplaygroundismyoffice"
wrote in message ...

Can anyone help me on this? I'm trying to figure out the number of days
that it takes to do a project excluding weekends, but I keep getting
NAME errors whenever I try to use the formula that Excel Help has for
Workday and don't know if there is another formula to use to get the
info I want... any help on this would be appreciated!!! So far I've
been able to compute the number of days including weekends by
subtracting my start date from my end date, but that number doesn't
really give me what I need, as I want to know how many WORKDAYS it
takes. The Workday formula is based off a start date plus however many
workdays, so that doesn't help because that is the info I am lacking.
HELP?!

-VB

...wanna de-stress? try a stress ball from Office Playground!!! :)


--
myplaygroundismyoffice
------------------------------------------------------------------------
myplaygroundismyoffice's Profile:
http://www.msusenet.com/member.php?userid=2247
View this thread: http://www.msusenet.com/t-1870545398

Use NETWORKDAYS(). See HELP for details.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"myplaygroundismyoffice"
wrote in message ...

Can anyone help me on this? I'm trying to figure out the number of days
that it takes to do a project excluding weekends, but I keep getting
NAME errors whenever I try to use the formula that Excel Help has for
Workday and don't know if there is another formula to use to get the
info I want... any help on this would be appreciated!!! So far I've
been able to compute the number of days including weekends by
subtracting my start date from my end date, but that number doesn't
really give me what I need, as I want to know how many WORKDAYS it
takes. The Workday formula is based off a start date plus however many
workdays, so that doesn't help because that is the info I am lacking.
HELP?!

-VB

...wanna de-stress? try a stress ball from Office Playground!!! :)


--
myplaygroundismyoffice
------------------------------------------------------------------------
myplaygroundismyoffice's Profile:
http://www.msusenet.com/member.php?userid=2247
View this thread: http://www.msusenet.com/t-1870545398

Disregard my previous answer; Arvi is right

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"myplaygroundismyoffice"
wrote in message ...

Can anyone help me on this? I'm trying to figure out the number of days
that it takes to do a project excluding weekends, but I keep getting
NAME errors whenever I try to use the formula that Excel Help has for
Workday and don't know if there is another formula to use to get the
info I want... any help on this would be appreciated!!! So far I've
been able to compute the number of days including weekends by
subtracting my start date from my end date, but that number doesn't
really give me what I need, as I want to know how many WORKDAYS it
takes. The Workday formula is based off a start date plus however many
workdays, so that doesn't help because that is the info I am lacking.
HELP?!

-VB

...wanna de-stress? try a stress ball from Office Playground!!! :)


--
myplaygroundismyoffice
------------------------------------------------------------------------
myplaygroundismyoffice's Profile:
http://www.msusenet.com/member.php?userid=2247
View this thread: http://www.msusenet.com/t-1870545398

Ok, I do not have access to the MS Excel CD to install this with. Is
there a way to download it from a MS website, or am I going to have to
hunt down the program install cd for this? (egads, I hope not! I have
no idea where that thing went!!!!) Anyway, I'm about ready to just
forget it and use a calendar so I can just get it done and off my to-do
list. Any ideas for a formula that would do this without having to use
the add in toolpak? Thanks for the response!!! -VB


--
myplaygroundismyoffice
------------------------------------------------------------------------
myplaygroundismyoffice's Profile: http://www.msusenet.com/member.php?userid=2247
View this thread: http://www.msusenet.com/t-1870545398

On Fri, 17 Jun 2005 15:56:24 -0500, myplaygroundismyoffice
wrote:

Ok, I do not have access to the MS Excel CD to install this with. Is
there a way to download it from a MS website, or am I going to have to
hunt down the program install cd for this? (egads, I hope not! I have
no idea where that thing went!!!!) Anyway, I'm about ready to just
forget it and use a calendar so I can just get it done and off my to-do
list. Any ideas for a formula that would do this without having to use
the add in toolpak? Thanks for the response!!! -VB

Here is a UDF that does not require the ATP to be installed. It was written to
be a bit more flexible than the WORKDAY function, but you can use it in a
similar fashion.

To enter this, <alt<F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, enter a formula of the type:

=WrkDay(StartDate,NumDays,Holidays)

The Holidays argument is optional and, if present, refers to a range where you
have the holiday dates listed.


===========================
Function WrkDay(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 1, _
Optional WeekendDay_2 As Integer = 7, _
Optional WeekendDay_3 As Integer = 0) As Date

' Sunday = 1; Monday = 2; ... Saturday = 7

Dim i As Long
Dim TempDate As Date
Dim Stp As Integer
Dim NonWrkDays As Long
Dim temp As Long, SD As Date, ED As Date

Stp = Sgn(NumDays)

'Add NumDays
TempDate = StartDate + NumDays

'Add Non-Workdays

Do While Abs(NumDays) < temp
SD = Application.WorksheetFunction.Min(StartDate + Stp, TempDate)
ED = Application.WorksheetFunction.Max(StartDate + Stp, TempDate)

temp = NWrkDays(SD, ED, Holidays, WeekendDay_1, WeekendDay_2, WeekendDay_3)
TempDate = TempDate + NumDays - Stp * (temp)
Loop

WrkDay = TempDate
End Function
=======================


--ron

On Fri, 17 Jun 2005 21:21:13 -0400, Ron Rosenfeld
wrote:

On Fri, 17 Jun 2005 15:56:24 -0500, myplaygroundismyoffice
wrote:

Ok, I do not have access to the MS Excel CD to install this with. Is
there a way to download it from a MS website, or am I going to have to
hunt down the program install cd for this? (egads, I hope not! I have
no idea where that thing went!!!!) Anyway, I'm about ready to just
forget it and use a calendar so I can just get it done and off my to-do
list. Any ideas for a formula that would do this without having to use
the add in toolpak? Thanks for the response!!! -VB


Oops -- you need more than what I posted for the UDF. I neglected to include
the NWrkDays UDF which is required for this. So paste in the code below,
instead of what I posted in the last message.

The use of the function remains the same.

===========================
Function NWrkDays(StartDate As Date, EndDate As Date, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 1, _
Optional WeekendDay_2 As Integer = 7, _
Optional WeekendDay_3 As Integer = 0) As Long
' Sunday = 1; Monday = 2; ... Saturday = 7

'credits to Myrna

Dim i As Long
Dim Count As Long
Dim H As Variant
Dim w As Long
Dim SD As Date, ED As Date
Dim DoHolidays As Boolean
Dim NegCount As Boolean

DoHolidays = Not (Holidays Is Nothing)

SD = StartDate: ED = EndDate
If SD ED Then
SD = EndDate: ED = StartDate
NegCount = True
End If

w = Weekday(SD - 1)
For i = SD To ED
Count = Count + 1
w = (w Mod 7) + 1
Select Case w
Case WeekendDay_1, WeekendDay_2, WeekendDay_3
Count = Count - 1
Case Else
If DoHolidays Then
If IsNumeric(Application.Match(i, Holidays, 0)) Then _
Count = Count - 1
End If
End Select
Next i
If NegCount = True Then Count = -Count
NWrkDays = Count
End Function

Function WrkDay(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 1, _
Optional WeekendDay_2 As Integer = 7, _
Optional WeekendDay_3 As Integer = 0) As Date

' Sunday = 1; Monday = 2; ... Saturday = 7

Dim i As Long
Dim TempDate As Date
Dim Stp As Integer
Dim NonWrkDays As Long
Dim temp As Long, SD As Date, ED As Date

Stp = Sgn(NumDays)

'Add NumDays
TempDate = StartDate + NumDays

'Add Non-Workdays

Do While Abs(NumDays) < temp
SD = Application.WorksheetFunction.Min(StartDate + Stp, TempDate)
ED = Application.WorksheetFunction.Max(StartDate + Stp, TempDate)

temp = NWrkDays(SD, ED, Holidays, WeekendDay_1, WeekendDay_2, WeekendDay_3)
TempDate = TempDate + NumDays - Stp * (temp)
Loop

WrkDay = TempDate
End Function
==========================
--ron

On Fri, 17 Jun 2005 21:50:18 +0300, "Arvi Laanemets"
wrote:

To use WORKDAYS function, you must have Analysis Toolpack Ad-In installed .

As I discovered, in Excel 2003 at least, that means Analysis Toolpak
and Analysis Toolpak VBA.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Modern cyberspace is a deadly festering swamp, teeming with
dangerous programs such as "viruses," "worms," "Trojan horses,"
and "licensed Microsoft software" that can take over your
computer and render it useless. --Dave Barry