A UDF is a Used Defined Function. This is VBA, so it goes into a standard
code module. Go into the VBE (Alt-F11), insert a code module (menu
InsertModule), and paste that code into it.
You just use it as any other worksheet function.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Aviator" wrote in message
...
I am not sure what UDF is. Were would I put this information?
"Bob Phillips" wrote:
Aviator,
It can be done with formulae, but they can be very complex. I prefer to
have
a UDF that does it. This is such a UDF that I have adapted from one of
my
date UDFs. You would use it like so
=fWORKDAY(D$5,1,holidays,FALSE)
The last FALSE is important otherwise it just works like a normal
WORKDAY
function.
'---------------------------------------------------------------------
Function fWORKDAY(StartDate As Variant, _
Days As Variant, _
Optional Holidays, _
Optional IncFri As Boolean = True)
'---------------------------------------------------------------------
Dim cDays As Long, cDays2 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
EndDate = Application.Run("ATPVBAEN.XLA!WORKDAY", StartDate, Days,
Holidays)
'add extra days to cover fridays
'determine the friday after end date
If (Not IncFri) Then
cDays2 = IIf(Weekday(StartDate, vbSunday) = vbFriday, 6, 7)
EndDateWE = EndDate + (cDays2 - Weekday(EndDate, vbSunday))
cDays = cDays + ((EndDateWE - (StartDate + 1)) \ 7)
If Weekday(EndDate, vbSunday) = vbFriday Then cDays = cDays + 1
End If
If (cDays 0) Then
fReEntry = True
EndDate = fWORKDAY(StartDate:=EndDate, _
Days:=cDays, _
Holidays:=Holidays, _
IncFri:=IncFri)
End If
fWORKDAY = EndDate
fReEntry = False
Exit Function
DF_errValue_exit:
fWORKDAY = CVErr(xlErrValue)
End Function
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Aviator" wrote in message
...
This works great. The only problem is that we only work Monday thru
Thusday.
That is why I was using:
=IF(D$6="","",IF(WEEKDAY(D$6+1,2)4,D$6+1+(WEEKDAY (D$6+1)2)*7-WEEKDAY(D$6+1
)+2,D$6+1))
Is there a way to accomplish both 4 day work weeks and skipping the
holidays?
Thank you,
Aviator
"Bob Phillips" wrote:
Aviator,
Why not just WORKDAY
=IF(D$6="","",workday(D6,1,holidays))
where holidays is the holidays range
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Aviator" wrote in message
...
The following formula works well it just does not exclude the
holidays.
Calendar Formula without Weekends (Col. E)
=IF(D$6="","",IF(WEEKDAY(D$6+1,2)4,D$6+1+(WEEKDAY (D$6+1)2)*7-WEEKDAY(D$6+1
)+2,D$6+1))
I can not get this to work.
Calendar Formula without Weekends & Holidays (Col. E)
=IF(NETWORKDAYS(D$23,D$23,AZ2:AZ27)=1,IF(D$23=""," ",IF(WEEKDAY(D$23+1,2)4,D
$23+1+(WEEKDAY(D$23+1)2)*7-WEEKDAY(D$23+1)+2,D$23+1)),"Weekend/Holiday")
The following is how I have things set up.
Formula:
1. Input date scheduled to start: 12/23
2.
=IF(D$40="","",IF(WEEKDAY(D$40+1,2)4,D$40+1+(WEEK DAY(D$40+1)2)*7-WEEKDAY(D
$40+1)+2,D$40+1))
3.
=IF(E$40="","",IF(WEEKDAY(E$40+1,2)4,E$40+1+(WEEK DAY(E$40+1)2)*7-WEEKDAY(E
$40+1)+2,E$40+1))
4.
=IF(F$40="","",IF(WEEKDAY(F$40+1,2)4,F$40+1+(WEEK DAY(F$40+1)2)*7-WEEKDAY(F
$40+1)+2,F$40+1))
What I have currently:
1 2 3 4
Col. D Col. E Col. F Col. G
12/23 12/27 12/28 12/29
This is the way I want it to look like.
Wanting to Skip Weekends & Holidays:
1 2 3 4
Col. D Col. E Col. F Col. G
12/23 1/3 1/4 1/5
Formula:
1 Input date scheduled to start: 12/23
2 ?
3 ?
4 ?
Note: AZ2:AZ25 are the listed holidays
Please Help! Thank you,
Aviator
|