Need Help Computing Workdays for a project

[myplaygroundismyoffice]

Can anyone help me on this? I'm trying to figure out the number of day
that it takes to do a project excluding weekends, but I keep gettin
NAME errors whenever I try to use the formula that Excel Help has fo
Workday and don't know if there is another formula to use to get th
info I want... any help on this would be appreciated!!! So far I'v
been able to compute the number of days including weekends b
subtracting my start date from my end date, but that number doesn'
really give me what I need, as I want to know how many WORKDAYS i
takes. The Workday formula is based off a start date plus however man
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!!! :

 

[Arvi Laanemets]

Hi

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


Arvi Laanemets

 

[Niek Otten]

Use NETWORKDAYS(). See HELP for details.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Niek Otten]

Disregard my previous answer; Arvi is right

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[myplaygroundismyoffice]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Stan Brown]

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