About Us

On Thu, 11 Aug 2005 12:00:20 -0700, "keith m" <keith
wrote:

I am using the formula "=Workday" but I want this formula to include
Saturdays. I have a Start Date and a duration and want to calculate the end
date with Saturday included as a work day. Can anyone tell me how to do
this? Any assistance would be greatly appreciated. Thank you.

Here is a UDF (User Defined Function) that will accomplish that.

To enter this function, <alt<F11 opens the Visual Basic 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 the function, in some cell enter the formula:

=WDincSat(Start,NumDays,Holidays)

The variables can be cell references or entered directly. The Holidays
argument can be a range; but it is optional.

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

'Workday function without Analysis Toolpak

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) = 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) = vbSunday Then _
TempDate = TempDate + Stp + (Stp < 0)
End If
Loop
End If
Next i

WDincSat = TempDate
End Function
========================================

HTH,
--ron

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Work book formulas	Jessica	Excel Discussion (Misc queries)	1	June 9th 05 10:51 AM
How to copy the work sheets from different workbooks into one?	Sesh	Excel Discussion (Misc queries)	1	April 15th 05 06:03 PM
Starting work period on a Saturday and ending on a friday	JLyons	Excel Worksheet Functions	3	February 18th 05 01:13 PM
Starting work period on a Saturday and ending on a friday	JLyons	Excel Discussion (Misc queries)	1	February 17th 05 02:28 PM
I really need help! Changing work period start dates	JLyons	Excel Worksheet Functions	0	February 16th 05 01:19 PM

Menu

About Us