Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28

    Unanswered: Calculating days Mon to Friday excluding weekends

    Dates seem to be my nemesis!

    I have two feilds DateStart and DateEnd.
    I have a query based on these [DateEnd]-[DateStart] which gives the total number of days between but I only want the number of working days (ie. Monday to Friday Inclusive) within the date ranges. I do not need to worry about other holidays.


    Note: Within the query there is a field [FinDate] that converts Null values in the DateEnd to todays date.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Have a look in the code bank and see if that helps.

    Cheers!
    Me.Geek = True

  3. #3
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28
    Wwooa,
    I'm still at level beginner 101, maybe 104, and this looks looks like advanced 909 and while impressive I have no idea what to do with it.
    I was hoping for some form of criteria or simple addition to the SQL code that would point to some inbuilt ability in Access..
    Everything I can find identifies Mondays thru Fridays in single fields but not when they are asked to be identified with a date range.
    I really appreciate the pointer but as I said I don't know what to do with it.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    heh heh, no problem
    I don't believe Access has anything "built-in" that will do what you're asking, but I'll see if I can at least help you out to accomplish it.

    If you only need to worry about not counting weekends, then I would suggest looking here for a start (else if you need holidays, this site has a similar function to include them). It starts to explain what to do with the code, but it's assuming you know a thing or two already. No worries, here's what you do.

    In the main access window, go the Public Modules section and create a new one. It may have some text in it already (like "Option Explicit" or something), but at the end of that paste the code from the above site. Save the module.

    Now that you've told Access what this function is and what to do with it, it's time to use it. Go to your query, and do something like the following:

    SELECT ..., Work_Days([tblName].[fldDateBeginningName], [tblName].[fldDateEndingName]), ...

    Plug in you table names and field names appropriately; If you need more help with this, let us know the table name and field names in question.

    By the way, if you'd like to learn more about dates and times in Access, have a look here.

    If I didn't explain myself very well or missed something, let me know. Cheers!
    Me.Geek = True

  5. #5
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28
    I have read through the Microsoft knowledge base article and have tried to add the sub at the bottom of the screen. But the bit I can't work out is how to select which 2 dates it creates the calculation between or how to link it to the [DateEnd] and [DateStart] in my tables?





    Sub Work_Dates()

    Dim BegDate As Variant
    Dim EndDate As Variant
    Dim Work_Days As Variant

    ' Note that this function does not account for holidays.

    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 Sub

    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 Sub
    Else
    ' If some other error occurs, provide a message.
    MsgBox "Error " & Err.Number & ": " & Err.Description
    End If
    End Sub

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    You don't have to "link it to your tables" or anything of the sort. If you've created a public module and pasted the code into it, you've just told your application what this function Work_Dates does. By the way, you need to copy it exactly from the ms page into your module, I noticed you no longer have the arguments in the first line.

    Once you have the function in place, then you can use it very easily via SQL by just calling out the function along with the two dates in question. If you post your SQL (and tell me which fields you're trying to calc' between) I'll see if I can help you out some more.

    Cheers!
    Me.Geek = True

  7. #7
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28
    Hi Nick

    Thank you for the quick reply. I have edited the module and changed it to exactly the same as it is in the knowledge base article.

    The SQL from my query is below the 2 dates that I need to work out the number of working days between are [DPStart] and [EndCalc]

    SELECT Q_Test.CgID, Q_Test.ClientID, Q_Test.DPStart, Q_Test.DPEnd, Q_Test.EndCalc
    FROM Q_Test;

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    Then try:

    SELECT Q_Test.CgID, Q_Test.ClientID, Q_Test.DPStart, Q_Test.DPEnd, Q_Test.EndCalc, Work_Days(Q_Test.DPStart, Q_Test.DPEnd) AS [Work Days]
    FROM Q_Test;

    See if that works for you.
    Me.Geek = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •