inconsistency with DateDiffExlude command

[DaveB]

Hi there, I'm really hoping someone can help me with an issue I am having using the ‘DateDiffExclude’ command to work out the number of working days between two dates.

I have created a complaints database which is used to log all complaints coming into the company. Upon entering a Resolved Date I want the database to populate a field [Resolved_on_elapsed_day_number ] with the number of working days it took to complete the complaint. This is simply by calculating the difference between the date that the complaint was raised [add_date] and the date that the complaint was closed [Date_resolved]. I have the following code in place (this occurs whenever the [Date_resolved] field is changed)
__________________

Private Sub Date_resolved_Change()
Me.Resolved_on_elapsed_day_number = DateDiffExclude(me.add_date, Me.Date_resolved, "17")
DoCmd.Requery
End Sub
__________________

To my understanding the ‘17’ in the formula is to establish which dates are considered working days. 1 being Sunday, 7 being Saturday

The code is essentially working, but there are certain instances where it just returns a 0 as the value. For example.

[add_date] = 26/08/2010
[Date_resolved] = 01/09/2010
[Resolved_on_elapsed_day_number ]= 0

Whereas if I amend the [Resolved_on_elapsed_day_number ] field by one date it will display the correct result, for example:

[add_date] = 26/08/2010
[Date_resolved] = 02/09/2010
[Resolved_on_elapsed_day_number ]= 6

[add_date] = 26/08/2010
[Date_resolved] = 31/08/2010
[Resolved_on_elapsed_day_number ]= 4

It is annoying because the code works, it has proven that it works by giving the correct results in most cases and it accounts for weekends but then in some instances it just returns 0. Any help would be most appreciated as I am going round in circles now trying to find a solution to this.

Please see attached image for an example of the form being used.

 

[missinglinq]

Screen grabs are seldom of any value in helping with problems, but posting the code for DateDiffExclude(), which I'm guessing is a custom function, might help.

Not sure exactly what you mean by

if I amend the [Resolved_on_elapsed_day_number ] field by one date

either.

Linq ;0)>

 

[Dairy Farmer]

Have a look at:

http://www.mvps.org/access/datetime/date0012.htm

 

[DaveB]

Thanks for the replies guys.

@MissingLing; Sorry, my mistake, it is a custom function. I can't remember my source for getting it now but I thought it was working when I first set it up but after a few months of use I have identified these issues.

here is the code for the function:
____________________________________________________
Function DateDiffExclude(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:
' ? DateDiffExclude(#2/10/06#, #2/13/06#, "17")
'Output: 2
'*********************************************

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

DateDiffExclude = FullWeek + OddDays

End Function
____________________________________________________

Also, when I said 'if I amend the [Resolved_on_elapsed_day_number ] field by one date'... I meant, if I change the Date_resolved by 1 day (for example 02/09/2010 to 01/09/2010) then it returns the correct result. Sorry, it's tricky to explain it clearly sometimes.

Thanks for your help, much appreciated.