Calculating Business Days

[ahuntley15]

I am trying to calculate transit times in an access query for a large amount of shipments and need to exclude weekends. I have tried using the SQL functions:

MyDays: CntDays([StartDate],[EndDate])

and

SELECT [StartDte], [EndDte], DateDiffExclude2([StartDte], [EndDte], "17") as WkDays


... in normal queries but to no luck. I have copy and pasted the code into new modules for each of these functions but am still receiving this error: 'undefined function: cntdays in expression.'

Also, when the SQL states Start date and end date do I need to enter the two date fields already entered into the database or the numbers 2 and 6 for monday through friday?

Any help would be greatly appreciated.

 

[RuralGuy]

Search this site for Workdays.

 

[HiTechCoach]

I am trying to calculate transit times in an access query for a large amount of shipments and need to exclude weekends. I have tried using the SQL functions:

MyDays: CntDays([StartDate],[EndDate])

and

SELECT [StartDte], [EndDte], DateDiffExclude2([StartDte], [EndDte], "17") as WkDays


... in normal queries but to no luck. I have copy and pasted the code into new modules for each of these functions but am still receiving this error: 'undefined function: cntdays in expression.'

Also, when the SQL states Start date and end date do I need to enter the two date fields already entered into the database or the numbers 2 and 6 for monday through friday?

Any help would be greatly appreciated.

Place the following code in a new module:

' Created 2-14-2006 by Peter M. Schroeder
' Modified 5-20-2006 by Peter M. Schroeder (to correct an issue where the "end" date comes before the "start" date)
' Credits to Doug Steele, MVP, for the initial "weekdays between" calculation (first two lines of the main formula)
' Assumes a table named tblHolidays with a field HolidayDate exists

Public Function gfncNetWorkdays(pdatStart As Date, pdatEnd As Date) As Long

  gfncNetWorkdays = DateDiff("d", pdatStart, pdatEnd) - (DateDiff("ww", pdatStart, pdatEnd, 1) * 2) - _
                    IIf(Weekday(pdatEnd, 1) = 7, IIf(Weekday(pdatStart, 1) = 7, 0, 1), IIf(Weekday(pdatStart, 1) = 7, -1, 0)) + _
                    IIf(Weekday(IIf(pdatStart <= pdatEnd, pdatStart, pdatEnd), 2) < 6, IIf(pdatStart <= pdatEnd, 1, -1), 0) - _
                    (Nz(DCount("*", "tblHolidays", "HolidayDate Between #" & pdatStart & "# and  #" & pdatEnd & "# AND Weekday(HolidayDate,2) < 6"), 0) * _
                     IIf(pdatStart <= pdatEnd, 1, -1))

End Function

name the module modWorkDays

then you can use:

MyDays: gfncNetWorkdays([StartDate],[EndDate])

 

[ahuntley15]

I've cut and past your code into a new module and saved as ModWorkDays but whenever they query is run the same error comes up: "The Form name "Form2" is misspelled or refers to a form that doesn't exsit." The code and the SQL do not contain the word "Form2" is this a common error?

Also when specifying start date and end date I have been using the formula:

MyDays: gfncNetWorkdays([Plant Date],[Date])

I would like the formula to calculate the transit time for each shipment but does this formula require actual dates to be entered in for "start date" and "end date" or are the field references listed above adequate? Thank you for your help!

 

[raskew]

Hi -

I did a Goggle on CntDays() and found one entry at http://en.allexperts.com/q/Using-MS-Access-1440/2008/1/Count-number-weekdays-M.htm.

As written, it was missing an end line and would return 0 for any entry. If this is what you're using, it should read:

Function CntDays(Date1 As Date, Date2 As Date) As Long
'source:http://en.allexperts.com/q/Using-MS-Access-1440/2008/1/Count-number-weekdays-M.htm
Dim DayCntr  As Long
Dim LoopDate As Date
Dim WD       As Long

DayCntr = 0
For LoopDate = Date1 To Date2
   WD = WeekDay(LoopDate)
   If WD > 1 And WD < 7 Then '1-Sunday 7-Saturday
      DayCntr = DayCntr + 1
   End If
Next LoopDate
[COLOR="Red"]CntDays = DayCntr 'added[/COLOR]
End Function

Function DateDiffExclude2() which I wrote, should work. Just in case, here it is again:

Function DateDiffExclude2(pstartdte As Date, _
                         penddte As Date, _
                         pexclude As String) As Integer
'*********************************************
'Purpose:   Return number of days between two
'           dates, excluding weekdays(Sun = 1
'           thru Sat = 7) specified by user
'Coded by:  raskew
'Inputs:    from debug (immediate) window:
'           -excludes Saturday (7) and Sunday (1) from count
'           ? DateDiffExclude2(#2/10/06#, #7/13/06#, "17")
'Output:    110
'*********************************************

Dim WeekHold  As String
Dim WeekKeep  As String
Dim FullWeek  As Integer
Dim OddDays   As Integer
Dim n         As Integer

    WeekHold = "1234567123456"
    'get # of full weeks (7 days) & convert to # of days
    FullWeek = Int((penddte - pstartdte + 1) / 7) * (7 - Len(pexclude))
    'get # of days remaining after FullWeek is determined
    OddDays = (penddte - pstartdte + 1) Mod 7
    'string representation of the weekdays contained in OddDays
    WeekKeep = Mid(WeekHold, WeekDay(pstartdte), OddDays)
    'use boolean statement to reduce OddDays by 1 for each
    'pexclude weekday found in WeekKeep
    For n = 1 To Len(pexclude)
      OddDays = OddDays + (InStr(WeekKeep, Mid(pexclude, n, 1)) > 0)
    Next n
    
    DateDiffExclude2 = FullWeek + OddDays

End Function

HiTechCoach's function calls for tblHolidays. Do you have such a table?

Be advised that all of the functions are mutually exclusive. Use one without any reference to the others.

MyDays: gfncNetWorkdays([Plant Date],[Date])

In any of the functions, if referring to today's date, use Date() -- no brackets since [Date] would refer to a field named "Date". If using actual dates, see my example.

"The Form name "Form2" is misspelled or refers to a form that doesn't exsit."

That tells me that something else is going on that we are not seeing.

Bob

 

[raskew]

Here is a much simplified business day function:

Public Function fGetWorkdays2(pstart As Date, pend As Date) As Integer
'************************************************
'Purpose:   Compute number of workdays (Mon - Fri)
'           between pStart and pEnd
'Coded by:  raskew
'Input:    ? fGetWorkdays2(date(), #4/1/09#)
'Output:    51
'************************************************

   fGetWorkdays2 = 7 - WeekDay(pstart) + 5 * (DateDiff("ww", pstart, pend) - 1) + WeekDay(pend) - 1

End Function

Bob

 

[JCShort]

Bob,
You are a scholar and a gentleman & my very own personal savior for the day!!! I've been trying to calculate weekday differences using different methods for almost an entire workday at this point. I love this site and appreciate the expertise that all of you demonstrate and share.

I'm using Access 2003. Out of curiosity, do you know why the DateDiff() interval "w" yields the exact same result as "ww"? That's frustrating as all get out! The help search states that "w" is supposed to be weekday intervals, but it calculates whole weeks anyway.