workday formula

  • Thread starter Thread starter Mary
  • Start date Start date
Not trivial.

Using the variable definitions of the WORKDAY function, try:

=start_date+INT(days/6)*7+MOD(days,6)+(MOD(days,6)>7-WEEKDAY(start_date))-AN
D(WEEKDAY(start_date)=1,MOD(days,6)=0)

This will need extensive testing and is not reliable for nonpositive values
of the variable "days". The last part with the AND is a very crude fix
because the formula was not working when "start_date" was a Sunday and
"days" was a multiple of 6, and I didn't have the energy to debug it.

I hope you don't need to provide for holidays as well!
 
How do I have the workday formula include Saturdays?
Click to expand...

There is no particular argument to allow that.

This UDF is one way of doing it. Note that the test for Saturday is commented
out. The UDF was written to give a WORKDAY equivalent without using the
Analysis ToolPak, and should work regardless of one's region.

The disadvantage of this approach is that it will run much slower than the
built-in functions. However, if you don't have many instances of the formula,
that should not be an issue.

======================================
Function WD(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 = Abs(NumDays) / 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
 
Oops, I forgot to comment out the second test for Saturday.


How do I have the workday formula include Saturdays?
Click to expand...

There is no particular argument to allow that.

This UDF is one way of doing it. Note that the test for Saturday is commented
out. The UDF was written to give a WORKDAY equivalent without using the
Analysis ToolPak, and should work regardless of one's region.

The disadvantage of this approach is that it will run much slower than the
built-in functions. However, if you don't have many instances of the formula,
that should not be an issue.

======================================
Function WD(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 = Abs(NumDays) / 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
 
Mary said:
How do I have the workday formula include Saturdays?
Click to expand...

Give this a try; it is a variation of a formula picked up from Aladin.

=SUMPRODUCT(--ISNUMBER(MATCH(WEEKDAY(ROW(INDIRECT(F146+1&":"&G146))),{2,3,4,5,6,7},0)))
 
Back
Top