You don't need a UDF

First one

=IF(AND(NOT(ISNUMBER(MATCH(start_date,holidays,0)) ),WEEKDAY(start_date,2)<numDays),WORKDAY(start_dat e,numDays,holidays),"")

Second

=IF(OR(ISNUMBER(MATCH(start_date,holidays,0)),WEEK DAY(start_date,1)=1),"",
start_date+SIGN(numDays)*SMALL(IF((WEEKDAY(start_d ate+SIGN(numDays)*(ROW(INDIRECT("1:"&ABS(numDays)* 10))),2)<7)*
ISNA(MATCH(start_date+SIGN(numDays)*(ROW(INDIRECT( "1:"&ABS(numDays)*10))),holidays,0)),ROW(INDIRECT( "1:"&ABS(numDays)*10))),ABS(numDays)))

which is an array formula

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Hello,

can anybody help me to build a user defined function "myWORKDAY"

typical with excel's built-in function =WORKDAY(start_date,days,holidays)

but the UDF must refuse to call a start_date if it is within the holidays
or
non-workday(s).

also, to allow me make some adjustments on my scheduling criteria.

e.g.
UDF#1 name "my5WORKDAY" for a Regular Monday to Friday work schedule
UDF#2 name "my6WORKDAY" for a extended Monday to Saturday work schedule.

I believe that this forum have come for a long way of getting task be
solved
easily...

more power
dribler

Bob,

is this possible or not ?
please advice..

happy holidays


"Bob Phillips" wrote:

You don't need a UDF

First one

=IF(AND(NOT(ISNUMBER(MATCH(start_date,holidays,0)) ),WEEKDAY(start_date,2)<numDays),WORKDAY(start_dat e,numDays,holidays),"")

Second

=IF(OR(ISNUMBER(MATCH(start_date,holidays,0)),WEEK DAY(start_date,1)=1),"",
start_date+SIGN(numDays)*SMALL(IF((WEEKDAY(start_d ate+SIGN(numDays)*(ROW(INDIRECT("1:"&ABS(numDays)* 10))),2)<7)*
ISNA(MATCH(start_date+SIGN(numDays)*(ROW(INDIRECT( "1:"&ABS(numDays)*10))),holidays,0)),ROW(INDIRECT( "1:"&ABS(numDays)*10))),ABS(numDays)))

which is an array formula

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Hello,

can anybody help me to build a user defined function "myWORKDAY"

typical with excel's built-in function =WORKDAY(start_date,days,holidays)

but the UDF must refuse to call a start_date if it is within the holidays
or
non-workday(s).

also, to allow me make some adjustments on my scheduling criteria.

e.g.
UDF#1 name "my5WORKDAY" for a Regular Monday to Friday work schedule
UDF#2 name "my6WORKDAY" for a extended Monday to Saturday work schedule.

I believe that this forum have come for a long way of getting task be
solved
easily...

more power
dribler

Here's a pretty generic UDF, which handles a 5 day, 6 day or 7 day week.
Call like so

=fnworkday(start_date,numDays,holidays)

for a 5 day week

=fnworkday(start_date,numDays,holidays,TRUE)

for a 6 day week

=fnworkday(start_date,numDays,holidays,TRUE,TRUE)

for a 7 day week


Option Explicit

Dim fReEntry As Boolean

'---------------------------------------------------------------------
Function fnWorkday(ByVal StartDate, _
ByVal Days, _
Optional ByVal Holidays, _
Optional ByVal IncSat As Boolean = False, _
Optional ByVal 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 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

If (cDays 0) Then
fReEntry = True
EndDate = fnWorkday(StartDate:=EndDate, _
Days:=cDays, _
Holidays:=Holidays, _
IncSat:=IncSat, _
IncSun:=IncSun)
End If

fnWorkday = EndDate

fReEntry = False

Exit Function

DF_errValue_exit:
fnWorkday = 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

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Bob,
i am still testing the other UDF u gave me, and Don.s advice, it seems to
work but i will give a sure reply upon testing it to my larger
spreadsheet.

this post really needs a short UDF as the one u gave me. to simplify my
works in excel...I have been run-off by other co-ms program in task
scheduling due to long formulation...

thanks for the time

"Bob Phillips" wrote:

You don't need a UDF

First one

=IF(AND(NOT(ISNUMBER(MATCH(start_date,holidays,0)) ),WEEKDAY(start_date,2)<numDays),WORKDAY(start_dat e,numDays,holidays),"")

Second

=IF(OR(ISNUMBER(MATCH(start_date,holidays,0)),WEEK DAY(start_date,1)=1),"",
start_date+SIGN(numDays)*SMALL(IF((WEEKDAY(start_d ate+SIGN(numDays)*(ROW(INDIRECT("1:"&ABS(numDays)* 10))),2)<7)*
ISNA(MATCH(start_date+SIGN(numDays)*(ROW(INDIRECT( "1:"&ABS(numDays)*10))),holidays,0)),ROW(INDIRECT( "1:"&ABS(numDays)*10))),ABS(numDays)))

which is an array formula

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Hello,

can anybody help me to build a user defined function "myWORKDAY"

typical with excel's built-in function
=WORKDAY(start_date,days,holidays)

but the UDF must refuse to call a start_date if it is within the
holidays
or
non-workday(s).

also, to allow me make some adjustments on my scheduling criteria.

e.g.
UDF#1 name "my5WORKDAY" for a Regular Monday to Friday work schedule
UDF#2 name "my6WORKDAY" for a extended Monday to Saturday work
schedule.

I believe that this forum have come for a long way of getting task be
solved
easily...

more power
dribler

thanks Bob,

Wow, it works well on the 5 day and 7 day WORKDAY

Yet the 6 day, incsat

seems giving me an extra result of one day.

i tried the 3 formula for the same data

start date: 02/12/2006
days: 33
Holidays : 25/12/2006, 1/01/2007

the results a
5 workday: 20/01/2007 : perfect !
7 workday:06/01/2007 : perfect !

6 workday: 13/01/2007 : should have been 12/01/2007

hope we can resolve this as a helpful post with a clear check mark..

regards
driller