PDA

View Full Version : Micro for TAT



online
03-02-2010, 05:52 AM
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
03-02-2010, 06:49 AM
What is wrong with the WORKDAY function?

online
03-04-2010, 01:24 AM
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
03-04-2010, 02:47 AM
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>=MIN(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
03-04-2010, 03:54 AM
Dear Xld,

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

Bob Phillips
03-04-2010, 04:33 AM
Why should it be 3, 4 looks right to me.

Bob Phillips
03-04-2010, 04:36 AM
Is this the problem

For i = 0 To (LastDate - FirstDate)

should it be

For i = 1 To (LastDate - FirstDate)

online
03-04-2010, 05:03 AM
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
03-04-2010, 05:47 AM
What are the rules for time, the impact on the number of days?

online
03-05-2010, 12:42 AM
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))