How do I include Saturdays to be counted in the WORKDAY function?

Assuming A1 is the start date, A2 is the duration, and B5:B10 is the holiday
range, what should I put as the formula so that in A3, the end date would be
calculated to give me the WORKDAY function, but including the Saturdays?
currently, if i use the workday function in

=IF(WEEKDAY(WORKDAY(A1,A2,B5:B10)-1,1)=1,WORKDAY(A1,A2,B5:B10)-3,WORKDAY(A1,A2,B5:B10)-1)

it would give me the end date of the program after measuring the duration
from the start date, (start date inclusive). However, I need to have it
include saturdays. is there another way? or should I modify this formula?

One way

http://tinyurl.com/4bbcr


Regards,

Peo Sjoblom

"kippi3000" wrote:

How do I include Saturdays to be counted in the WORKDAY function?

Assuming A1 is the start date, A2 is the duration, and B5:B10 is the holiday
range, what should I put as the formula so that in A3, the end date would be
calculated to give me the WORKDAY function, but including the Saturdays?
currently, if i use the workday function in

=IF(WEEKDAY(WORKDAY(A1,A2,B5:B10)-1,1)=1,WORKDAY(A1,A2,B5:B10)-3,WORKDAY(A1,A2,B5:B10)-1)

it would give me the end date of the program after measuring the duration
from the start date, (start date inclusive). However, I need to have it
include saturdays. is there another way? or should I modify this formula?

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)


"kippi3000" wrote in message
...
How do I include Saturdays to be counted in the WORKDAY function?

Assuming A1 is the start date, A2 is the duration, and B5:B10 is the
holiday
range, what should I put as the formula so that in A3, the end date would
be
calculated to give me the WORKDAY function, but including the Saturdays?
currently, if i use the workday function in


=IF(WEEKDAY(WORKDAY(A1,A2,B5:B10)-1,1)=1,WORKDAY(A1,A2,B5:B10)-3,WORKDAY(A1,
A2,B5:B10)-1)

it would give me the end date of the program after measuring the duration
from the start date, (start date inclusive). However, I need to have it
include saturdays. is there another way? or should I modify this formula?

Hi
a formula solution (if you don't want to use a VBA UDF function):
=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKD AY(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(da ys)*10))),2)<7)*ISNA(MATCH(start_date+SIGN(days)*( ROW(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW (INDIRECT("1:"&ABS(days)*10))),ABS(days)))

This is an array formula to be entered with CTRL+SHIFT+ENTER. So in your
example use:
=A1+IF(A2=0,0,SIGN(A2)*SMALL(IF((WEEKDAY(A1+SIGN(A 2)*(ROW(INDIRECT("1:"&ABS(A2)*10))),2)<7)*ISNA(MAT CH(A1+SIGN(A2)*(ROW(INDIRECT("1:"&ABS(A2)*10))),ho lidays,0)),ROW(INDIRECT("1:"&ABS(A2)*10))),ABS(A2) ))

and ofr holidays insert the range containing your holiday dates

--
Regards
Frank Kabel
Frankfurt, Germany

kippi3000 wrote:
How do I include Saturdays to be counted in the WORKDAY function?

Assuming A1 is the start date, A2 is the duration, and B5:B10 is the
holiday range, what should I put as the formula so that in A3, the
end date would be calculated to give me the WORKDAY function, but
including the Saturdays? currently, if i use the workday function in

=IF(WEEKDAY(WORKDAY(A1,A2,B5:B10)-1,1)=1,WORKDAY(A1,A2,B5:B10)-3,WORKDAY(A1,A2,B5:B10)-1)

it would give me the end date of the program after measuring the
duration from the start date, (start date inclusive). However, I need
to have it include saturdays. is there another way? or should I
modify this formula?

Hi Bob

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 possible (using a kind of trick to just add enough dyas to take care
of any sensible number of holidays). See my post (though not a very simple
function I've to admit but you could replace the WORKDAY function with 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)

Like that!
Frank

Mr Kabel,

That is a fantastic formula, but I think you must be sadder even than I to
spend time working all that through :-)

Bob

"Frank Kabel" wrote in message
...
Hi
a formula solution (if you don't want to use a VBA UDF function):

=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKD AY(start_date+SIGN(days)*(
ROW(INDIRECT("1:"&ABS(days)*10))),2)<7)*ISNA(MATCH (start_date+SIGN(days)*(RO
W(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW(I NDIRECT("1:"&ABS(days)*10)
)),ABS(days)))

This is an array formula to be entered with CTRL+SHIFT+ENTER. So in your
example use:

=A1+IF(A2=0,0,SIGN(A2)*SMALL(IF((WEEKDAY(A1+SIGN(A 2)*(ROW(INDIRECT("1:"&ABS(
A2)*10))),2)<7)*ISNA(MATCH(A1+SIGN(A2)*(ROW(INDIRE CT("1:"&ABS(A2)*10))),holi
days,0)),ROW(INDIRECT("1:"&ABS(A2)*10))),ABS(A2)))

and ofr holidays insert the range containing your holiday dates

--
Regards
Frank Kabel
Frankfurt, Germany

kippi3000 wrote:
How do I include Saturdays to be counted in the WORKDAY function?

Assuming A1 is the start date, A2 is the duration, and B5:B10 is the
holiday range, what should I put as the formula so that in A3, the
end date would be calculated to give me the WORKDAY function, but
including the Saturdays? currently, if i use the workday function in


=IF(WEEKDAY(WORKDAY(A1,A2,B5:B10)-1,1)=1,WORKDAY(A1,A2,B5:B10)-3,WORKDAY(A1,
A2,B5:B10)-1)

it would give me the end date of the program after measuring the
duration from the start date, (start date inclusive). However, I need
to have it include saturdays. is there another way? or should I
modify this formula?

Bob
lol
good thing Formula was already nearly ready to copy and paste. Just
replacing the correct weekday check was all that was needed.
Also this was a nice challenge that it is feasible. Now I'm only waiting for
Harlan to simplify this formula ;-)

"Bob Phillips" wrote:

Mr Kabel,

That is a fantastic formula, but I think you must be sadder even than I to
spend time working all that through :-)

Bob

"Frank Kabel" wrote in message
...
Hi
a formula solution (if you don't want to use a VBA UDF function):

=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKD AY(start_date+SIGN(days)*(
ROW(INDIRECT("1:"&ABS(days)*10))),2)<7)*ISNA(MATCH (start_date+SIGN(days)*(RO
W(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW(I NDIRECT("1:"&ABS(days)*10)
)),ABS(days)))

This is an array formula to be entered with CTRL+SHIFT+ENTER. So in your
example use:

=A1+IF(A2=0,0,SIGN(A2)*SMALL(IF((WEEKDAY(A1+SIGN(A 2)*(ROW(INDIRECT("1:"&ABS(
A2)*10))),2)<7)*ISNA(MATCH(A1+SIGN(A2)*(ROW(INDIRE CT("1:"&ABS(A2)*10))),holi
days,0)),ROW(INDIRECT("1:"&ABS(A2)*10))),ABS(A2)))

and ofr holidays insert the range containing your holiday dates

--
Regards
Frank Kabel
Frankfurt, Germany

kippi3000 wrote:
How do I include Saturdays to be counted in the WORKDAY function?

Assuming A1 is the start date, A2 is the duration, and B5:B10 is the
holiday range, what should I put as the formula so that in A3, the
end date would be calculated to give me the WORKDAY function, but
including the Saturdays? currently, if i use the workday function in


=IF(WEEKDAY(WORKDAY(A1,A2,B5:B10)-1,1)=1,WORKDAY(A1,A2,B5:B10)-3,WORKDAY(A1,
A2,B5:B10)-1)

it would give me the end date of the program after measuring the
duration from the start date, (start date inclusive). However, I need
to have it include saturdays. is there another way? or should I
modify this formula?

Thanks.. I'm actually very new in Excel programming.. i'm just a college
student who just began learning Excel.. so your reply originally didn't help
much.. instructions weren't that clear..

basically what i did was copy from

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



and pasted it into a new module in in VB

is this formula (=datefrom()) usable in my pc from now on, or is it only
applicable to this workbook?

thanks for the help! you certainly helped solve a big problem on my part!

BTW, your formula's duration doesn't include the start date! what i did was
minus 1 off the duration!

Dear Frank Kabel,

I really appreciate the help given. However, your formula didn't help out
the way i hoped it to be.. It only gave me the right end date for durations
of 0 and 1. if the duration is 2 and above, ot would give me an error..

However, i really appreciated the help given in this community workgroup! i
really learnt a lot from the posts!

In fact, i have just learnt about the "sign" and "indirect" functions after
reading your post!

thanks a lot!

Hi
what exact formula have you used and have you entered this formula as array
formula?

--
Regards
Frank Kabel
Frankfurt, Germany
"kippi3000" schrieb im Newsbeitrag
...
Dear Frank Kabel,

I really appreciate the help given. However, your formula didn't help out
the way i hoped it to be.. It only gave me the right end date for
durations
of 0 and 1. if the duration is 2 and above, ot would give me an error..

However, i really appreciated the help given in this community workgroup!
i
really learnt a lot from the posts!

In fact, i have just learnt about the "sign" and "indirect" functions
after
reading your post!

thanks a lot!