Count Days Between Two Dates and Exclude Weekends

[Myriad_Rocker]

I'm in somewhat of a pickle, I guess. I was hoping to be able to do this all in a query instead of doing a query and then using VB to do my day calculation and then somehow spitting that back out. This is for a web app, so I'm using ASP.Net with VB code behind. I'm trying to establish a data source for my page. Anyway...on to the issue.

I want to get the number of days between two dates but NOT include the weekends/holidays in the count.

So, if the start date is a Friday and the end date is a Monday, the day count is 1.

Any ideas?

 

[RuralGuy]

Have you searched this forum for Business Days?

 

[raskew]

Hi -

This will return the number of days between two dates, excluding those days as specified by the operator (e.g. Sunday = 1, Saturday = 7)

As written, it counts both the start and end dates so, to correspond with your example, you'll need to subtract one day from the function's output.

To use, copy/paste to a standard module, then call as shown in the example.

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

HTH - Bob

 

[Myriad_Rocker]

Hi -

This will return the number of days between two dates, excluding those days as specified by the operator (e.g. Sunday = 1, Saturday = 7)

As written, it counts both the start and end dates so, to correspond with your example, you'll need to subtract one day from the function's output.

To use, copy/paste to a standard module, then call as shown in the example.

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

HTH - Bob

Thanks, Bob. Question for you, though. Can I call a function that is stored in Access from VB.Net in my query string??

 

[raskew]

In SQL you'd incorporate the function something like:

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

Bob

 

[Myriad_Rocker]

In SQL you'd incorporate the function something like:

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

Bob

I had already tried and it didn't work.

 

[MSAccessRookie]

I had already tried and it didn't work.

What error are you getting? I do things like this all of the time. I would have suggested a function, but he beat me to it.

 

[Myriad_Rocker]

What error are you getting? I do things like this all of the time. I would have suggested a function, but he beat me to it.

I don't remember but I've abandoned that approach. I tried the function call for a function stored in Access from my VB.net code behind when I passed the SQL through in a string variable and it didn't work. That's all I remember.