Workday Function Question

  • Thread starter Thread starter hapyhrt
  • Start date Start date
H

hapyhrt

I am using the workday function to build a schedule. This function
counts the workweek as Monday thru Friday, but I'm working with a
Monday thru Saturday workweek. Is there any way to adjust the
function? Or maybe there is anther type of workaround?
Thanks for any help.
 
I am using the workday function to build a schedule. This function
counts the workweek as Monday thru Friday, but I'm working with a
Monday thru Saturday workweek. Is there any way to adjust the
function? Or maybe there is anther type of workaround?
Click to expand...

From a previous post of mine....

Here is a function I have posted in the compiled VB newsgroups (modified for
your requirement that Saturday is a workday) which calculates the number of
workdays (Monday thru Saturday) between any two dates, but it does not
account for Holidays (which vary by countries and, within the US, even by
individual states)...

Function WorkDays(StartDate As Date, EndDate As Date) As Long
Dim D As Date
Dim NumWeeks As Long
NumWeeks = (EndDate - StartDate) \ 7
WorkDays = NumWeeks * 6
For D = (StartDate + NumWeeks * 7) To EndDate
If Weekday(D) > 1 Then WorkDays = WorkDays + 1
Next
End Function

It is fast because the maximum number of iteration in the loop is 6. The
holidays part of your question will have to be dealt with using a separate
loop. I would probably store the Holidays (within some maximum range of
dates) in an array and loop the array seeing if the individual Holiday dates
falls within, or on, the span covered by StartDate and EndDate and subtract
one for each date doing so.

Rick
 
You could use this User Defined Function (UDF) from Ron Rosenfeld.
Instructions for implementing a UDF at the bottom of this message

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

' ===========================
' Ron Rosenfeld
' Copied form Google's Newsgroup Archives April 27, 2006

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
' ==========================
================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
steps:

Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).
From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and then
press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
Press ALT+F11 again to return to your Excel worksheet.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================


|I am using the workday function to build a schedule. This function
| counts the workweek as Monday thru Friday, but I'm working with a
| Monday thru Saturday workweek. Is there any way to adjust the
| function? Or maybe there is anther type of workaround?
| Thanks for any help.
|
 
Back
Top