User Function.

[Paulo]

hi,
50% of by hair loss this year is due to problems with Analyst tool pack.
syncing with excel on several computers

i came up with whis formula in excel
where FERIADOS = array of dates
=SE(F6>0;SE(OU(DIA.DA.SEMANA(F6)=7;DIA.DA.SEMANA(F6)=1;ÉNÚM(CORRESP(F6;FERIADOS;0)));workday((F6);2;FERIADOS);workday((F6);1;FERIADOS));0)
translating
=IF(F6>0;IF(OR(WEEKDAY(F6)=7;WEEKDAY(F6)=1;ISNUMBER(MACH(F6;FERIADOS;0)));workday((F6);2;FERIADOS);workday((F6);1;FERIADOS));0)

I would like some help on making it in VBA without needing Analyst tool pack

so the logic's of this
F6 = date
1) if F6 is Saturday, TRUE
2) if F6 is Sunday, TRUE
3) If 6 belongs to Holiday array, TRUE

if true then
date + 2 days skipping holidays
else
date + 1 days skipping holidays

my biggest problem is what the "workday" formula, do

any help is appreciate

 

[butrfly_kis3]

Paulo,

What are you trying to do exactly? I used a special formula to
calculate the number of days between two dates to include saturdays as a
normal workday. I also used a list of holiday's so to exlude these
dates from being calculated in. Maybe I can help.

Katie

Here is a sample code I used from another site:
Function NWD(StartDate As Date, EndDate As Date, Holiday As Date, _
Optional Holidays As Date, _
Optional WeekendDay_1 As Integer = 1, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0, _
Optional WeekendDay_4 As Integer = 0) As Long
' Sunday = 1; Monday = 2; ... Saturday = 7

'credits to Myrna

Dim i As Long
Dim Count As Long
Dim H As Variant
Dim w As Long
Dim SD As Date, ED As Date
Dim DoHolidays As Boolean
Dim NegCount As Boolean
Dim Holiday As Date


w = Weekday(SD - 1)
For i = SD To ED
Count = Count + 1
w = (w Mod 7) + 1
Select Case w
Case WeekendDay_1, WeekendDay_2, WeekendDay_3, WeekendDay_4
Count = Count - 1
Case Else
If DoHolidays Then
If IsNumeric(Application.Match(i, Holidays, 0)) Then _
Count = Count - 1
End If
End Select
DoHolidays = _
Date = Holiday
If StartDate Or EndDate = Holiday _
Then Count -Sum(Holiday)
End If

SD = StartDate: ED = EndDate
If SD > ED Then
SD = EndDate: ED = StartDate
NegCount = True
End If

Next i
If NegCount = True Then Count = -Count
NWD = Count
End Function

 

[Paulo]

thank you katie,
I will take a look @ it.