DateDiff() to only show weekdays?

[mcgraw]

I'm trying to run a simple query in Access 2007 to show the difference between two dates, but only have it calculate weekdays (I don't care about holidays).

I throught doing "w" would only show weekdays, but it is calcuating weeks.

SELECT 
tblWithGroup.[ID], 
tblWithGroup.[Issues_ID], 
tblWithGroup.[Group], 
tblWithGroup.[Start_Date], 
tblWithGroup.[End_Date], 
DateDiff("w",[start_date],[end_date]) AS [Total Days with Group]
FROM tblWithGroup;

Is there an "easy" way to calculate the # of weekdays?

The other question is, is it possible to have it return a 1 even if the start date and end date are the same date? (both start_date and end_date do track time based off the Now() function)

 

[raskew]

Hi -

Give this a try:

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

 

[mcgraw]

If I save that code as a module (called datediffexclude2) how do I call it in a query to see if it works for me?

Thanks for the help!

 

[Brianwarnock]

looking at the code and the explanation of input I would say
Totaldays: DateDiffExclude2([start_date],[end_date],"17")

Brian

 

[mcgraw]

When I try that, I get Undefined function 'DateDiffExclude2' in expression.

 

[boblarson]

When I try that, I get Undefined function 'DateDiffExclude2' in expression.

Make sure you've put that function into a STANDARD module (not a form or report module) and then named the module something DIFFERENT from the function.

 

[mcgraw]

once again Bob is a GOD. I'm going to start putting that up on billboards like they used to do for Clapton.

 

[boblarson]

once again Bob is a GOD. I'm going to start putting that up on billboards like they used to do for Clapton.

I don't think so. As I said, I have only been doing this for almost 13 years, so I should know a few things. But there are several here (LPurvis, datAdrenaline, and more) that could make me look like a newbie. It is all relative.

 

[mcgraw]

If they make you look like a newbie...then I must be the single celled organism that hasn't crawled out of the water yet. EVERYONE on this forum has been awesome and very helpful!

 

[boblarson]

Good luck on the rest of your project. And yes, this forum has some great people here. Glad that we've been able to help you along.