You can't use Saturday in the Workday function, you have to build your own.
The problem is the recursive nature of the calculation, that is when you
work out the end date, if it is a Sunday or a holiday, you need to
recalculate the new end date. I know of no way to do this in worksheet
functions, so I wrote a VBA UDF to do it.
It is a generic routine, so it can handle inc Saturdays or Sundays (or both
or non e), and also a holiday list like the WORKDAY Function.
In your case, call like
=DateFrom(startdate,numdays,holiday_list,TRUE)
Option Explicit
Dim fReEntry As Boolean
'---------------------------------------------------------------------
Function DateFrom(StartDate, _
Days, _
Optional Holidays, _
Optional IncSat As Boolean = False, _
Optional IncSun As Boolean = False)
'---------------------------------------------------------------------
' Function: Calculate the date that is 'Days' number of working
' days beyond 'StartDate'
' Synopsis: Recursive routine that adds the number of days passed
' in and the start date to get an end date.
' Calculates the number of Saturdays, Sundays, and
' holidays between these dates, and if not 0, calls
' itself for next iteration. When zero, exits with the
' latest end date
'---------------------------------------------------------------------
Dim cDays As Long, cDays2WE As Long
Dim StartDateWe As Date, EndDate As Date, EndDateWE As Date
Application.Volatile
'check if valid arguments
If (Not IsDate(StartDate)) Then
GoTo DF_errValue_exit
ElseIf (Not IsNumeric(Days)) Then
GoTo DF_errValue_exit
ElseIf Days < 1 Or IsEmpty(Days) Then
GoTo DF_errValue_exit
ElseIf (Not IsMissing(Holidays)) Then
If (TypeName(Holidays) <> "Range" And _
TypeName(Holidays) <> "String()" And _
TypeName(Holidays) <> "Variant()") Then
GoTo DF_errValue_exit
End If
End If
#If fDebug Then
Debug.Print StartDate & ", " & _
Days & ", " & _
IncSat & ", " & _
IncSun
#End If
'if start is sat and sun not included, move past
If Weekday(StartDate, vbSunday) = vbSaturday And Not IncSun Then
If Not fReEntry Then
Days = Days + 1
End If
End If
EndDate = StartDate + Days
'add any holidays between start and (current) end dates
If Not (IsMissing(Holidays)) Then
'use startdate + 1 as start will already accounted
cDays = NumHolidays(StartDate + 1, EndDate, Holidays, IncSat,
IncSun)
End If
'add extra days to cover saturdays
'determine the saturday after end date
cDays2WE = IIf(Weekday(StartDate, vbSunday) = vbSaturday, 6, 7)
EndDateWE = EndDate + (cDays2WE - Weekday(EndDate, vbSunday))
If (Not IncSat) Then
cDays = cDays + ((EndDateWE - StartDate) \ 7)
End If
'reduce by appropriate no of sundays
'determine the sunday after end date
cDays2WE = IIf(Weekday(StartDate, vbSunday) = vbSunday, 6, 7)
EndDateWE = EndDate + (cDays2WE - Weekday(EndDate, vbSunday))
If (Not IncSun) Then
cDays = cDays + ((EndDateWE - StartDate) \ 7)
End If
'allow for ending on sat or sun
' If (Weekday(EndDate, vbSunday) = vbSaturday And Not IncSat) Then
' cDays = cDays + IIf(IncSun, 1, 2)
' ElseIf (Weekday(EndDate, vbSunday) = vbSunday And Not IncSun) Then
' cDays = cDays + 1
' End If
If (cDays > 0) Then
fReEntry = True
EndDate = DateFrom(StartDate:=EndDate, _
Days:=cDays, _
Holidays:=Holidays, _
IncSat:=IncSat, _
IncSun:=IncSun)
End If
DateFrom = EndDate
fReEntry = False
Exit Function
DF_errValue_exit:
DateFrom = CVErr(xlErrValue)
End Function
'---------------------------------------------------------------------
Function NumHolidays(ByVal StartDate, _
ByVal EndDate, _
ByVal Holidays, _
ByVal IncSat As Boolean, _
ByVal IncSun As Boolean)
'---------------------------------------------------------------------
Dim cHolidays As Long
Dim cell
For Each cell In Holidays
' If (IsNumeric(cell.Value)) Then
If (CDate(cell.Value) >= StartDate And CDate(cell.Value) <=
EndDate) Then
cHolidays = cHolidays + 1
If (Weekday(CDate(cell), vbSunday) = vbSaturday) Then
If Not IncSat Then
cHolidays = cHolidays - 1
End If
ElseIf (Weekday(CDate(cell), vbSunday) = vbSunday) Then
If Not IncSun Then
cHolidays = cHolidays - 1
End If
End If
End If
' End If
Next cell
NumHolidays = cHolidays
End Function
--
HTH
RP
(remove nothere from the email address if mailing direct)
