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)

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

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!

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

Brian

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

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.

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

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.

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!

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.