Working Days Calculated (1 Viewer)

Diamond-Leopard

Registered User.
Local time
Today, 13:29
Joined
Oct 30, 2018
Messages
26
Hello Again My Wonderful Friends.... Here is todays problem :D

I have a field named "Date Ordered" and I have another field named "Date Delivered" and what I would like is a calculated field called "Working Days Taken To Deliver"

So as the name suggests I am looking an expression that I can pop in the calculated field and it will look at the two dates and work out how many working days (Mon-Fri) it has taken to arrive. Not including the date of order.


So if ordered on Friday the 3rd and Delievered on Tuesday 7th then the Working Days would be the Monday and Tuesday so 2 :) 2 Working Days

Thank you peeps, I know you'll smash it :D
 

Minty

AWF VIP
Local time
Today, 13:29
Joined
Jul 26, 2013
Messages
10,354
Have a read of the threads listed at the bottom of this one.

This is a very common topic.
If you get stuck implementing any of the solutions offered come back to us!
 

Diamond-Leopard

Registered User.
Local time
Today, 13:29
Joined
Oct 30, 2018
Messages
26
So I have looked through all of them and they have various codes to enter but the problem is im much stupider then you give me credit for... I'm not sure what I need to do with this code... I googled it and someone mentioned something about setting up a modual and then I guess you refer to the modual when you use it in the form? Is that right... I haven't used access for very long :(
 

Dreamweaver

Well-known member
Local time
Today, 13:29
Joined
Nov 28, 2005
Messages
2,466
Code:
'Call Sub like below for weekly           

BuildWorksSchedule StrDate, "ww", 1, WkDays, IntDays, -1




Sub BuildWorksSchedule(StrDate As Date, AddType As String, IntAdd As Integer, _
                        WkDays As Integer, IntTerm As Integer, IntStp As Variant)
Dim I As Integer, D As Integer
Dim CWd As Integer, Nd As Date

On Error GoTo HandleErr
'Make sure the first date added is included
StrDate = DateAdd(AddType, IntStp, StrDate)
    For I = 1 To IntTerm
        StrDate = DateAdd(AddType, IntAdd, StrDate)
        Nd = StrDate
        'Now we need to make sure it's an allowed working date
        'Add option for Saturday Working
        CWd = Weekday(Nd, vbMonday)
        If CWd > WkDays Then
            D = CWd - WkDays
            Nd = StrDate - D
        End If
        CreateWorksEntry (Nd)
    Next I
Me![NextRenewal] = DateAdd(AddType, IntAdd, StrDate)

HandleExit:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        Case Else
          MsgBox "The Following Error Has Occured & vbCrLf" _
                   & "Error Number: " & Err.Number & vbCrLf & "Error Description" _
                   & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select
End Sub
I know there Are a number of ways of doing what you want this is one i used in an old project
 
Last edited:

Cronk

Registered User.
Local time
Tomorrow, 00:29
Joined
Jul 4, 2013
Messages
2,770
@Diamond-Leopard

While it is easy to get the number of days between 2 dates (Date2 - Date1), to calculate working days you need to define what is a working day.

For most this is Mon through Fri, excluding public holidays. To get the number of working days will involve using code. Such code has been posted previously on this site and others, and involves looking up a table of public holidays and checking if the date range includes holidays.

To use it, you will need to have at lease some basic understanding of VBA. I suggest you search for an appropriate tutorial, or engage a consultant.
 

Diamond-Leopard

Registered User.
Local time
Today, 13:29
Joined
Oct 30, 2018
Messages
26
Code:
'Call Sub like below for weekly           

BuildWorksSchedule StrDate, "ww", 1, WkDays, IntDays, -1




Sub BuildWorksSchedule(StrDate As Date, AddType As String, IntAdd As Integer, _
                        WkDays As Integer, IntTerm As Integer, IntStp As Variant)
Dim I As Integer, D As Integer
Dim CWd As Integer, Nd As Date

On Error GoTo HandleErr
'Make sure the first date added is included
StrDate = DateAdd(AddType, IntStp, StrDate)
    For I = 1 To IntTerm
        StrDate = DateAdd(AddType, IntAdd, StrDate)
        Nd = StrDate
        'Now we need to make sure it's an allowed working date
        'Add option for Saturday Working
        CWd = Weekday(Nd, vbMonday)
        If CWd > WkDays Then
            D = CWd - WkDays
            Nd = StrDate - D
        End If
        CreateWorksEntry (Nd)
    Next I
Me![NextRenewal] = DateAdd(AddType, IntAdd, StrDate)

HandleExit:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        Case Else
          MsgBox "The Following Error Has Occured & vbCrLf" _
                   & "Error Number: " & Err.Number & vbCrLf & "Error Description" _
                   & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select
End Sub
I know there Are a number of ways of doing what you want this is one i used in an old project


So I am guessing this code needs to go in a button click event? I tried that and I am guessing there are parts of the code that I need to change but I really cant work it out.

Could you please just give me a clue as to what I should do with this code when I have moved it into my click event?
 

Diamond-Leopard

Registered User.
Local time
Today, 13:29
Joined
Oct 30, 2018
Messages
26
@Diamond-Leopard

While it is easy to get the number of days between 2 dates (Date2 - Date1), to calculate working days you need to define what is a working day.

For most this is Mon through Fri, excluding public holidays. To get the number of working days will involve using code. Such code has been posted previously on this site and others, and involves looking up a table of public holidays and checking if the date range includes holidays.

To use it, you will need to have at lease some basic understanding of VBA. I suggest you search for an appropriate tutorial, or engage a consultant.

Thank you for your incredibly helpful post suggesting that I engage a consultant , to be clear on what I am after here. I don't want to set up a holiday Inclusion. I just want Mon-Fri.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:29
Joined
Jan 23, 2006
Messages
15,362
For each date between date1 and date2, check to see if the date is not a Saturday or Sunday. If the date is Mon-Fri then add 1 to a variable (eg, GoodDateCnt integer). GoodDateCnt will be the value that meet this criteria.

Here is a function and test routine to review and adapt to your needs.
(Note: There is no error checking in this sample
Function

Code:
' ----------------------------------------------------------------
' Procedure Name: WeekdaysToDeliver
' Purpose: I am looking an expression that I can pop in the calculated field and
'it will look at the two dates and work out how many working days (Mon-Fri)
'it has taken to arrive. Not including the date of order.
' Procedure Kind: Function
' Procedure Access: Public
' Parameter OrderedDate (Date):
' Parameter DeliveredDate (Date):
' Return Type: Integer
' Author: Jack
' Date: 23-Nov-18
' ----------------------------------------------------------------
Function WeekdaysToDeliver(OrderedDate As Date, DeliveredDate As Date) As Integer
    'Dim OrderedDate As Date
    'Dim DeliveredDate As Date
    Dim MyDate As Date
    Dim GoodDayCnt As Integer
    'OrderedDate = #8/3/2018#
    'DeliveredDate = #8/7/2018#
    Debug.Print OrderedDate & "  is a " & WeekdayName(Weekday(OrderedDate))
    Debug.Print DeliveredDate & "  is a " & WeekdayName(Weekday(DeliveredDate))
    'To ignore OrderedDate add 1 to OrderedDate
    MyDate = OrderedDate + 1
    Debug.Print "Starting date for calculation is a " & WeekdayName(Weekday(MyDate))
    Do While MyDate <= DeliveredDate
        If Weekday(MyDate) >= 2 And Weekday(MyDate) <= 6 Then
            GoodDayCnt = GoodDayCnt + 1
            Debug.Print MyDate & "  is a " & WeekdayName(Weekday(MyDate))
        End If
        MyDate = MyDate + 1
    Loop
   WeekdaysToDeliver = GoodDayCnt
End Function

Test Routine

Code:
Sub testDeliverStuff()
Dim OrderDt As Date: OrderDt = #8/3/2018#
Dim DeliverDate As Date: DeliverDate = #8/7/2018#
Debug.Print "Weekdays taken to deliver is " & WeekdaysToDeliver(OrderDt, DeliverDate)
End Sub

Result

Code:
03-Aug-18  is a Friday
07-Aug-18  is a Tuesday
Starting date for calculation is a Saturday
06-Aug-18  is a Monday
07-Aug-18  is a Tuesday
Weekdays taken to deliver is 2

Good luck with your project
 
Last edited:

Diamond-Leopard

Registered User.
Local time
Today, 13:29
Joined
Oct 30, 2018
Messages
26
Thank you everyone for your help so far, I am struggling on and I feel like I might be getting there. Here is what I have found. It seems really simple but I cant get it to work.

Code:
Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
 
 Dim WholeWeeks As Variant
 Dim DateCnt As Variant
 Dim EndDays As Integer
 
 On Error GoTo Err_Work_Days
 
 BegDate = DateValue(BegDate)
 EndDate = DateValue(EndDate)
 WholeWeeks = DateDiff("w", BegDate, EndDate)
 DateCnt = DateAdd("ww", WholeWeeks, BegDate)
 EndDays = 0
 
 Do While DateCnt <= EndDate
 If Format(DateCnt, "ddd") <> "Sun" And _
 Format(DateCnt, "ddd") <> "Sat" Then
 EndDays = EndDays + 1
 End If
 DateCnt = DateAdd("d", 1, DateCnt)
 Loop
 
 Work_Days = WholeWeeks * 5 + EndDays
 
Exit Function
 
Err_Work_Days:
 
 ' If either BegDate or EndDate is Null, return a zero
 ' to indicate that no workdays passed between the two dates.
 
 If Err.Number = 94 Then
 Work_Days = 0
 Exit Function
 Else
' If some other error occurs, provide a message.
 MsgBox "Error " & Err.Number & ": " & Err.Description
 End If


This is the code I have found and I think it looks simple enough just to work out the week days. I didnt know where to put this code so I just put it at the very top of my form in VBA.

Then I tried the call function.. so I pressed call Work_Days and it asked me for the start date and end date so I typed

Code:
Call Work_Days(txtdatestart, txtdateend)
So everything looked tickateeboo and Now I am trying to get the answer to diisplay anywhere! lol I have tried in a text box or a label

I guess I need something like


Code:
txtAnswer = work_days

But that doesnt work...

I know I am so close to getting this working... Please help x
 

Diamond-Leopard

Registered User.
Local time
Today, 13:29
Joined
Oct 30, 2018
Messages
26
AHHHHHH so it was


Code:
txtAnswer = work-days(txtStart,txtEnd) As Integer

Thank you everyone x
 

Diamond-Leopard

Registered User.
Local time
Today, 13:29
Joined
Oct 30, 2018
Messages
26
Now that this is working I wonder if someone could suggest an edit?

I would like this not to count the start date if the start date is a week day?

Could someone show me how I would edit this code so that if the start date is a weekday It will exclude it? and start on the next working day?
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:29
Joined
Sep 21, 2011
Messages
14,041
Test the weekday number according to your system.?

By default Sun is 1 and Sat is 7, so 2 to 6 are weekdays if start of week is a Sunday?
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:29
Joined
Jan 23, 2006
Messages
15,362
See the code I gave in post 8, it shows how to ignore the starting date.
 

Users who are viewing this thread

Top Bottom