Weekends are not Friday and Saturday here (1 Viewer)

K

Krays23

Registered User.
Local time
Today, 17:16
Joined
Jun 18, 2015
Messages
46
Hi All

I know this is all over the place this topic however I need a solution as I cant seem to find a way to make it work.

In my company our weekends are Thursday Friday.

heres my issue

I have a access form from a table that gives me a customer required date that a job should be done on. on the same form I can enter the date actually done. its easy enough to get the delta / Diff between these two dates but what if one of those delta days is one of my weekends? I only want it to show working days I am behind the customer schedule!

Do any of you fantastic people have a solution for me ive been staring at this all day ad my eyes hurt!!!

TblMainData is where the data is layup is the first process and when complete a time will be placed in here and this will be measured against the layupCRS date cell for that row I need the days diff between those two dates if weekends are a Thursday Friday

For example below I know its 74 calendar days but in reality it should be less the 21 weekend days in that period and read 53 working days late

QryDeltaLayup ID Airbus MSN Strata Ship set Part in Assy out jig Assembly out of Jig Assy Out Of Jig CRS Expr1 67 85 SS 6 True 15/04/2016 01/02/2016 74


Hope you can help me!

Dan
 
RuralGuy

RuralGuy

AWF VIP
Local time
Today, 07:16
Joined
Jul 2, 2005
Messages
13,826
I don't know what code you're using but I'll bet it includes a function that allows you to change the 1st day of the week.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:16
Joined
May 7, 2009
Messages
19,237
use this function to calculate working days.

syntax: GetNetworkDays3([date start], [date end])

Code: 
Public Function GetNetWorkDays3( _
          ByVal datDateFrom As Variant, _
            ByVal datDateTo As Variant, _
                Optional ByVal strCountry As String, _
                    Optional ByVal booExcludeHolidays As Boolean) As Long
    Dim swp As Variant
    Dim i As Variant
    Dim lngDays As Long
    Dim strDateFrom As String
    Dim strDateTo As String
    Dim strFilter As String
    Dim strSQL As String
    Dim lngHolidays As Long
    Dim rs As DAO.Recordset
    Dim tempDate As Date
    
    ' Name of table with holidays.
    Const cstrTableHoliday    As String = "tbl_eDealHolidays"
    ' Name of date field in holiday table.
    Const cstrFieldHoliday    As String = "holDate"
    Const cstrCountryField    As String = "CountryCode"
    
    If IsNull(datDateFrom) Then datDateFrom = Date
    If IsNull(datDateTo) Then datDateTo = Date
    If datDateFrom > datDateTo Then
        swp = datDateFrom
        datDateFrom = datDateTo
        datDateTo = swp
    End If
    datDateFrom = CDate(Format(datDateFrom, "mm/dd/yyyy"))
    datDateTo = CDate(Format(datDateTo, "mm/dd/yyyy"))
    tempDate = datDateFrom
    While tempDate <= datDateTo
        If InStr("/Thursday/Friday/", Format(tempDate, "dddd")) = 0 Then
            lngDays = lngDays + 1
        End If
        tempDate = DateAdd("d", 1, tempDate)
    Wend
    
  If booExcludeHolidays And lngDays > 0 Then
    strDateFrom = Format(datDateFrom, "mm/dd/yyyy")
    strDateTo = Format(datDateTo, "mm/dd/yyyy")
    strFilter = "[" & cstrCountryField & "] = " & Chr(34) & strCountry & Chr(34) & " "
    strFilter = strFilter & " And " & _
            "[" & cstrFieldHoliday & "] Between #" & strDateFrom & "# And #" & strDateTo & "#;"
    strSQL = "SELECT [" & cstrFieldHoliday & "] FROM [" & cstrTableHoliday & "] " & _
            "WHERE " & strFilter
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            If InStr("/Thursday/Friday/", Format(rs(0).value, "dddd")) = 0 Then
                lngHolidays = lngHolidays + 1
            End If
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
  End If
    GetNetWorkDays3 = lngDays - lngHolidays
End Function
 
K

Krays23

Registered User.
Local time
Today, 17:16
Joined
Jun 18, 2015
Messages
46
Hi

Im not using any code at moment just date diff so it showing straight calendar days what I need is a code for Fridays sat as weekd=ends and sunday as first work day

in excel this is simple now with workdays.int function
 
K

Krays23

Registered User.
Local time
Today, 17:16
Joined
Jun 18, 2015
Messages
46
arnelgp said:
use this function to calculate working days.

syntax: GetNetworkDays3([date start], [date end])

Code: 
Public Function GetNetWorkDays3( _
          ByVal datDateFrom As Variant, _
            ByVal datDateTo As Variant, _
                Optional ByVal strCountry As String, _
                    Optional ByVal booExcludeHolidays As Boolean) As Long
    Dim swp As Variant
    Dim i As Variant
    Dim lngDays As Long
    Dim strDateFrom As String
    Dim strDateTo As String
    Dim strFilter As String
    Dim strSQL As String
    Dim lngHolidays As Long
    Dim rs As DAO.Recordset
    Dim tempDate As Date
    
    ' Name of table with holidays.
    Const cstrTableHoliday    As String = "tbl_eDealHolidays"
    ' Name of date field in holiday table.
    Const cstrFieldHoliday    As String = "holDate"
    Const cstrCountryField    As String = "CountryCode"
    
    If IsNull(datDateFrom) Then datDateFrom = Date
    If IsNull(datDateTo) Then datDateTo = Date
    If datDateFrom > datDateTo Then
        swp = datDateFrom
        datDateFrom = datDateTo
        datDateTo = swp
    End If
    datDateFrom = CDate(Format(datDateFrom, "mm/dd/yyyy"))
    datDateTo = CDate(Format(datDateTo, "mm/dd/yyyy"))
    tempDate = datDateFrom
    While tempDate <= datDateTo
        If InStr("/Thursday/Friday/", Format(tempDate, "dddd")) = 0 Then
            lngDays = lngDays + 1
        End If
        tempDate = DateAdd("d", 1, tempDate)
    Wend
    
  If booExcludeHolidays And lngDays > 0 Then
    strDateFrom = Format(datDateFrom, "mm/dd/yyyy")
    strDateTo = Format(datDateTo, "mm/dd/yyyy")
    strFilter = "[" & cstrCountryField & "] = " & Chr(34) & strCountry & Chr(34) & " "
    strFilter = strFilter & " And " & _
            "[" & cstrFieldHoliday & "] Between #" & strDateFrom & "# And #" & strDateTo & "#;"
    strSQL = "SELECT [" & cstrFieldHoliday & "] FROM [" & cstrTableHoliday & "] " & _
            "WHERE " & strFilter
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            If InStr("/Thursday/Friday/", Format(rs(0).value, "dddd")) = 0 Then
                lngHolidays = lngHolidays + 1
            End If
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
  End If
    GetNetWorkDays3 = lngDays - lngHolidays
End Function
Click to expand...

Hi does this look at Thursday Friday as holidays?

im not confident on how I use this could u explain a bit for me sorry
 
RuralGuy

RuralGuy

AWF VIP
Local time
Today, 07:16
Joined
Jul 2, 2005
Messages
13,826
I think this should do it!
Code: 
Public Function NetWorkdays(dteStart As Date, dteEnd As Date) As Integer
   '-- Returns a negative number if dteStart is > dteEnd
   Dim intGrossDays As Integer
   Dim dteCurrDate As Date
   Dim i As Integer
   intGrossDays = Abs(DateDiff("d", dteStart, dteEnd))
   NetWorkdays = 0
   For i = 0 To intGrossDays
      dteCurrDate = dteStart + i
      If Weekday(dteCurrDate, [B][COLOR="Red"]vbSaturday[/COLOR][/B]) < 6 Then
         NetWorkdays = NetWorkdays + 1
      End If
   Next i
   If dteStart > dteEnd Then
      NetWorkdays = NetWorkdays * -1
   End If
End Function
 
K

Krays23

Registered User.
Local time
Today, 17:16
Joined
Jun 18, 2015
Messages
46
RuralGuy said:
I think this should do it!
Code: 
Public Function NetWorkdays(dteStart As Date, dteEnd As Date) As Integer
   '-- Returns a negative number if dteStart is > dteEnd
   Dim intGrossDays As Integer
   Dim dteCurrDate As Date
   Dim i As Integer
   intGrossDays = Abs(DateDiff("d", dteStart, dteEnd))
   NetWorkdays = 0
   For i = 0 To intGrossDays
      dteCurrDate = dteStart + i
      If Weekday(dteCurrDate, [B][COLOR=red]vbSaturday[/COLOR][/B]) < 6 Then
         NetWorkdays = NetWorkdays + 1
      End If
   Next i
   If dteStart > dteEnd Then
      NetWorkdays = NetWorkdays * -1
   End If
End Function
Click to expand...

Hi I cant get any of these to work sorry

can you tell me where you add that in the query?

example 10/10/16 start and 20/10/16 end shoud return 8 days as there are 2 x Thursdays and 1 Friday in there
 
RuralGuy

RuralGuy

AWF VIP
Local time
Today, 07:16
Joined
Jul 2, 2005
Messages
13,826
What does your query look like? post the SQL for it please.
 
RuralGuy

RuralGuy

AWF VIP
Local time
Today, 07:16
Joined
Jul 2, 2005
Messages
13,826
The function goes in a Standard module so that it may be used in a query. I usually name the Module basUtilities.
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:16
Joined
Sep 12, 2006
Messages
15,653
given that it's only a few clock ticks, I often don't bother with smarts

simply amend this sort of thing to filter out the days you don't want

Code: 
 workingdays= 0
 for thedate from startdate to endate
    if weekday(thedate)<>vbfriday and weekday(thedate)<>vbthursday then
        workingdays=workingdays+1
    end if
 next
 
You must log in or register to reply here.

Similar threads

M
Solved Need to count the number of a specific day between two dates in access vba
Replies
9
Views
994
BAntar
B
B
Dlookup problem
2
Replies
21
Views
832
ebs17
E
M
reoccurring orders
Replies
7
Views
621
Pat Hartman
P
M
Solved Database not updating from Citrix ...
Replies
18
Views
552
Marshall Brooks
M
D
Transportation Database Designs
Replies
6
Views
911
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom