Urgent Support Required to exclude Weenkend

[majid.pervaiz]

I am a new user, I have followed from this forum and manage to calculate turn around time for tasks within department.
We have weekend here on Friday and Saturday, I believe the below code is for Saturday and Sunday...

can someone please help me to fix this issue to exclude Friday and Saturday from the total time calculation.


Public Function NetWorkhours(dteStart As Date, dteEnd As Date, Spellout As Boolean) As Variant
Dim intGrossDays As Integer
Dim intGrossMins As Single
Dim dteCurrDate As Date
Dim i As Integer
Dim WorkDayStart As Date
Dim WorkDayend As Date
Dim nonWorkDays As Integer
Dim StartDayMins As Single
Dim EndDayMins As Single
Dim NetworkMins As Integer
NetworkMins = 0
nonWorkDays = 0
'Calculate work day hours on 1st and last day
WorkDayStart = DateValue(dteEnd) + TimeValue("07:15:00")
WorkDayend = DateValue(dteStart) + TimeValue("14:30:00")
StartDayMins = DateDiff("n", dteStart, WorkDayend)
EndDayMins = DateDiff("n", WorkDayStart, dteEnd)
'adjust for time entries outside of business hours

'Calculate total hours and days between start and end times
intGrossDays = DateDiff("d", (dteStart), (dteEnd))
intGrossMins = DateDiff("n", (dteStart), (dteEnd))
'count number of weekend days and holidays (from a table called "Holidays" that lists them)
For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If Weekday(dteCurrDate, vbSaturday) < 3 Then
nonWorkDays = nonWorkDays + 1
Else
'If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & Int(dteCurrDate) & "#")) Then
'nonWorkDays = nonWorkDays + 1
'End If
End If
Next i
'Calculate number of work hours
Select Case intGrossDays
Case 0
'start and end time on same day
NetworkMins = (intGrossMins - ((nonWorkDays) * 1440))
Case 1
'start and end time on consecutive days
NetworkMins = StartDayMins + EndDayMins
Case Is > 1
'start and end time on non consecutive days
NetworkMins = (((intGrossDays - 1) - nonWorkDays) * 480) + (StartDayMins + EndDayMins)

End Select
If Spellout = True Then
NetWorkhours = MinsToTime(NetworkMins) ' hours and mins
Else
NetWorkhours = NetworkMins ' minutes only
End If


End Function
Function MinsToTime(Mins As Integer) As String
MinsToTime = Mins \ 60 & " hour" & IIf(Mins \ 60 <> 1, "s ", " ") & Mins Mod 60 & " minute" & IIf(Mins Mod 60 <> 1, "s", "")
End Function

 

[arnelgp]

you may edit this part:

For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If Weekday(dteCurrDate, vbSaturday) < 3 Then
nonWorkDays = nonWorkDays + 1
Else
'If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & Int(dteCurrDate) & "#")) Then
'nonWorkDays = nonWorkDays + 1
'End If
End If
Next i


To:


For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If Format(dteCurrDate, "ddd") = "Fri" Or Format(dteCurrDate, "ddd") = "Sat" Then
nonWorkDays = nonWorkDays + 1
Else
'If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & Int(dteCurrDate) & "#")) Then
'nonWorkDays = nonWorkDays + 1
'End If
End If
Next i

 

[sneuberg]

I thought I answered this post but I guess it didn't get saved. You just need to change the line

If Weekday(dteCurrDate, vbSaturday) < 3 Then

to

If Weekday(dteCurrDate, vbFriday) < 3 Then

 

[majid.pervaiz]

Bro Arnelgp and Sneuberg,

million million thanks for your expert advice.... it worked.

I am really thankful to you guys.

 

[majid.pervaiz]

Friends - just testing the above scenario, I have put date request date as Friday (15-Jan-16 10:13:00 AM) and response date as Sunday (17-Jan-16 11:26:00 AM).
Since we don't work on Friday and Saturday, SLA should start counting Sunday 07:15 which means the total time taken should be 04:11 or 251 minutes.
Note: we receive request from our International locations who work on Friday.

I have attached sample screenshot also.

as usual your expert advise required.

 

[majid.pervaiz]

Friends,

I believe there is some issue with the code, I have added request date Wednesday (02-Dec-15 10:13:00 AM) and response date Thursday (03-Dec-15 12:34:00 PM). if I calculate manually, the total time should be 10:34 or 634 minutes. However, in my result it is showing 576.

please guide me.

 

[sneuberg]

Apparently this code has problems. I suggest starting over with the code in post #17 in this thread http://www.access-programmers.co.uk/forums/showthread.php?t=183702&highlight=NetWorkhours&page=2

 

[majid.pervaiz]

Dear Sneuberg,

I will try and let you know.

many thanks for your prompt and excellent support. I owe you and arnelgp a dinner

 

[majid.pervaiz]

Dear Sneuberg,

I have slightly amended the code:
1. amended the start and end time
2. amended the total time of the day which is 435
3. amended the form through which this code will be called

But unfortunately, the result is not appearing in the form.

attaching word file with the completed code for your support.

I would be really grateful to you if you can support me to fix this code or let me know where I am making mistake.
Our working time is Sunday - Thursday (07:15 am until 2:30 pm)

 

[arnelgp]

are your work schedule changing every now and then.
if so, you must create new table for this.
there are two dates involved in your calculation, request date and response date.
udf networkhours function, is not for you.
you need a more customized function.

 

[sneuberg]

Dear Sneuberg,

I have slightly amended the code:
1. amended the start and end time
2. amended the total time of the day which is 435
3. amended the form through which this code will be called

But unfortunately, the result is not appearing in the form.

attaching word file with the completed code for your support.

I would be really grateful to you if you can support me to fix this code or let me know where I am making mistake.
Our working time is Sunday - Thursday (07:15 am until 2:30 pm)

I suggest you address your problems to the forum members at large rather than a specific member. Sorry, but I don't have time to work on this now. Also I suggest uploading a database with the problem instead of a word document. I believe you are more likely to get help that way.