Micro for TAT

[online]

Hi expert,

I have a micro fucntion which name is Getworkday for calculate TAT but its not remove holiday list. Its working but only not remove holiday list.

Code

Function GetWorkdays(FirstDate As Date, LastDate As Date, _
Optional Hols As Variant) As Integer
Dim i As Integer, ii As Integer, wkdys As Integer
Dim dy As Date
Dim f As Boolean
wkdys = 0
For i = 0 To (LastDate - FirstDate)
dy = CDate(FirstDate + i)
If Weekday(dy) <> 1 Then
f = False
If Not IsMissing(Hols) Then
For ii = 1 To Hols.Count
If Len(Hols(ii)) = 0 Then Exit For
If CDate(Hols(ii)) = dy Then
f = True
Exit For
End If
Next
End If
If Not f Then wkdys = wkdys + 1
End If
Next
GetWorkdays = wkdys
End Function

[Bob Phillips]

What is wrong with the WORKDAY function?

[online]

Dear,

I have to calculate TAT Excluding sunday and holiday between two date. If i calculate with networkday function then it exclude starday and sunday.

[Bob Phillips]

You can do it with a formula

=SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1-{2;3;4;5;6;7})-MIN(end_date,start_date)+8)/7))
-SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4;5;6;7},0))*(holidays>=MI N(end_date,start_date))*(holidays<=MAX(end_date,start_date)))

bu what is wrong with your UDF? I have given it a quick trial and it seems to work to m. Show an example where it doesn't work.

[online]

Dear Xld,

i have attached the sheet kindly check it and i need like this calculated formula if any plz guid me

[Bob Phillips]

Why should it be 3, 4 looks right to me.

[Bob Phillips]

Is this the problem

For i = 0 To (LastDate - FirstDate)

should it be

For i = 1 To (LastDate - FirstDate)

[online]

Dear

when i delete holiday date from E column then it should increase TAT value but it does not show any effect while i calculate networkdays its shows effect.
Suppose i have no of holidays list then what to do.
I hope there is date with timing issue. if i calculate remove time then it work correctly.

[Bob Phillips]

What are the rules for time, the impact on the number of days?

[online]

Dear,
i have done and thanx for your support

=IF(GetWorkdays(Q2,U2)=0,1,IF(HOUR(Q2)>15,GetWorkdays(TEXT(Q2,"mm/dd/yyyy"),U2,BE$1:BR$1)-2,GetWorkdays(TEXT(Q2,"mm/dd/yyyy"),U2,BE$1:BR$1)-1))