Calculate Business without the use of the Add-In (Analysis Tool-Pak)
 

How do I calculate business days without using the analysis Tool-Pak.


--
dannyboy213
------------------------------------------------------------------------
dannyboy213's Profile: http://www.excelforum.com/member.php...o&userid=31032
View this thread: http://www.excelforum.com/showthread...hreadid=507449

Calculate Business without the use of the Add-In (Analysis Tool-Pak)
 

On Wed, 1 Feb 2006 15:01:37 -0600, dannyboy213
wrote:


How do I calculate business days without using the analysis Tool-Pak.

How does this problem differ from the question you posted a few hours ago, and
to which you not only received responses, but you also responded that Bob's
solution worked?


--ron

Calculate Business without the use of the Add-In (Analysis Tool-Pak)
 

Wait for the next version of Excel, in which the ATP functions will be
integrated, I have understood.
Of course you can imitate the present BusinessDays functions of ATP, but it
sure is a lot of work.
What's your problem in using ATP?

--
Kind regards,

Niek Otten

"dannyboy213"
wrote in message
...

How do I calculate business days without using the analysis Tool-Pak.


--
dannyboy213
------------------------------------------------------------------------
dannyboy213's Profile:
http://www.excelforum.com/member.php...o&userid=31032
View this thread: http://www.excelforum.com/showthread...hreadid=507449

Calculate Business without the use of the Add-In (Analysis Tool-Pak)
 

Just to clarify, my last inquiry was to calculate the last business date
based on a given date. Now my current question is, how do I calculate
the business day from start date to end date w/o using the ATP. The
reason why I don't want to use the ATP is because not everyone who will
be viewing my file will have that add in.

Niek Otten Wrote:
Wait for the next version of Excel, in which the ATP functions will be
integrated, I have understood.
Of course you can imitate the present BusinessDays functions of ATP,
but it
sure is a lot of work.
What's your problem in using ATP?

--
Kind regards,

Niek Otten

"dannyboy213"

wrote in message
...

How do I calculate business days without using the analysis
Tool-Pak.


--
dannyboy213

------------------------------------------------------------------------
dannyboy213's Profile:
http://www.excelforum.com/member.php...o&userid=31032
View this thread:
http://www.excelforum.com/showthread...hreadid=507449



--
dannyboy213
------------------------------------------------------------------------
dannyboy213's Profile: http://www.excelforum.com/member.php...o&userid=31032
View this thread: http://www.excelforum.com/showthread...hreadid=507449

Calculate Business without the use of the Add-In (Analysis Tool-Pak)
 

On Wed, 1 Feb 2006 15:01:37 -0600, dannyboy213
wrote:


How do I calculate business days without using the analysis Tool-Pak.

Is it permissible to use a UDF (User Defined Function)? It would get
distributed with the workbook.

I had written one for just such an issue, and it basically mimics the Workday
Function.

If that would be helpful, I'd be happy to post it again.


--ron

Calculate Business without the use of the Add-In (Analysis Tool-Pak)
 

Hi Ron,

<I had written one for just such an issue, and it basically mimics the
Workday Function

Just curious:

What made you that?

--
Kind regards,

Niek Otten

"Ron Rosenfeld" wrote in message
...
On Wed, 1 Feb 2006 15:01:37 -0600, dannyboy213
wrote:


How do I calculate business days without using the analysis Tool-Pak.

Is it permissible to use a UDF (User Defined Function)? It would get
distributed with the workbook.

I had written one for just such an issue, and it basically mimics the
Workday
Function.

If that would be helpful, I'd be happy to post it again.


--ron

Calculate Business without the use of the Add-In (Analysis Tool-Pak)
 

Please do.

"Ron Rosenfeld" wrote in message
...
On Wed, 1 Feb 2006 15:01:37 -0600, dannyboy213
wrote:


How do I calculate business days without using the analysis
Tool-Pak.

Is it permissible to use a UDF (User Defined Function)? It would
get
distributed with the workbook.

I had written one for just such an issue, and it basically mimics
the
Workday
Function.

If that would be helpful, I'd be happy to post it again.


--ron


--
dannyboy213
------------------------------------------------------------------------
dannyboy213's Profile: http://www.excelforum.com/member.php...o&userid=31032
View this thread: http://www.excelforum.com/showthread...hreadid=507449

Calculate Business without the use of the Add-In (Analysis Tool-Pak)
 

On Thu, 2 Feb 2006 00:15:08 +0100, "Niek Otten" wrote:

Hi Ron,

<I had written one for just such an issue, and it basically mimics the
Workday Function

Just curious:

What made you that?

Same question was asked -- someone that wanted to use the Workday function, but
didn't want to install the ATP.

Best,
--ron

Calculate Business without the use of the Add-In (Analysis Tool-Pak)
 

On Wed, 1 Feb 2006 17:25:39 -0600, dannyboy213
wrote:


Please do.

"Ron Rosenfeld" wrote in message
.. .
On Wed, 1 Feb 2006 15:01:37 -0600, dannyboy213
wrote:


How do I calculate business days without using the analysis
Tool-Pak.

Is it permissible to use a UDF (User Defined Function)? It would
get
distributed with the workbook.

I had written one for just such an issue, and it basically mimics
the
Workday
Function.

If that would be helpful, I'd be happy to post it again.


--ron


To enter, <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, enter =WD(start_date, num_days, [holidays]) into some cell.

The optional 'holidays' argument must be entered as a cell (range) reference,
however.

==================================
Function WD(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing) As Date

'Workday function without Analysis Toolpak
'However, Holidays must be in a range

Dim i As Long
Dim TempDate As Date
Dim c As Range
Dim Stp As Integer

Stp = Sgn(NumDays)
TempDate = StartDate
For i = Stp To NumDays Step Stp
TempDate = TempDate + Stp
If Weekday(TempDate) = vbSaturday Then _
TempDate = TempDate + Stp - (Stp 0)
If Weekday(TempDate) = vbSunday Then _
TempDate = TempDate + Stp + (Stp < 0)

If Not Holidays Is Nothing Then
Do Until Not IsError(Application.Match(CDbl(TempDate), Holidays, 0)) = False
If IsError(Application.Match(CDbl(TempDate), Holidays, 0)) = False Then
TempDate = TempDate + Stp
If Weekday(TempDate) = vbSaturday Then _
TempDate = TempDate + Stp - (Stp 0)
If Weekday(TempDate) = vbSunday Then _
TempDate = TempDate + Stp + (Stp < 0)
End If
Loop
End If
Next i

WD = TempDate
End Function
===================================


--ron