Difference in days excluding weekend

[KevinSlater]

Hi,

i have the following criteria in a query to calculate the difference in days between two date fields, however i would like to not include any saturdays and sundays in the calculation.

DATEDIFFERNCE: DateDiff("d",[CC_ACK_PRINTED_DATE],[CC_LETTER_PRINTED_DATE])

Is this possible?

 

[Brianwarnock]

"w" instead of "d" gives you weekdays according to Access help.

Brian

 

[DCrake]

There is a sample mdb in the samples forum if you include this in your app you can do almost anything to do with dates missing and present holdiays and weekdays, etc.
Link


David

 

[KevinSlater]

i tried changing d to w as below, but it doesnt seem to calculate correctly
DATEDIFFERNCE: DateDiff("w",[CC_ACK_PRINTED_DATE],[CC_LETTER_PRINTED_DATE])

for example CC_ACK_PRINTED_DATE = 23/07/09 and CC_LETTER_PRINTED_DATE = 31/07/09 and DATEDIFFERNCE says: 1...maybe its calculating the number of weekends between the two dates.

 

[DCrake]

If I remember correctly Access only counts it as a week if there is a least 4 days in the range for that week. Plus you need to tell Access which day is the first day of the week.

David

 

[Brianwarnock]

Ok stopped working from memory and read help, although it calls it weekday it actually counts weeks but in a different way to ww.
The calculation is obviously more complex as you may have part weeks.
I would checkout the sample DBs as suggested by Dcrake if you don't want to reinvent the wheel.

Brian

 

[KevinSlater]

Ok thanks for the link David, that table looks helpful although i'm not to sure at present how to make use of this in the query. i guess i need to reference the weekend dates in the table to see of any of the dates in my query include weekend days

 

[raskew]

Hi -

Try saving this to a standard module, then call it from the debug (immediate) window as shown:

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

HTH - Bob

 

[KevinSlater]

raskew - thanks for the post but Please can you clarify what you mean? so copy that code into a new module then save it and call it from where?

 

[Brianwarnock]

You use it like any other function, in a query say

Numdays: DateDiffExclude(firstdate,seconddate,17)

The 17 represents sunday and saturday to be excuded, atleast that's how read it.

Brian

 

[KevinSlater]

ok i tried the following code in the criteria of the query:

Numdays: DateDiffExclude([CC_ACK_PRINTED_DATE],[CC_LETTER_PRINTED_DATE],17)

but for a lot of the record results it says: #Error

any suggestions on how to fix this?

 

[raskew]

Hi -

Two possibilities:
1) Either of the two dates is missing.
2) End date is < start date

Try modifying your query as follows:

SELECT tblDevProcess.Step, tblDevProcess.startTime, tblDevProcess.endTime, datediffexclude2([starttime],[endtime],"17") AS Expr1
FROM tblDevProcess
WHERE ((Not (tblDevProcess.startTime) Is Null) AND ((tblDevProcess.endTime)>=[starttime]));[/QUOTE]

Post back with more details if this doesn't resolve the issue.

Bob

 

[KevinSlater]

Ok thats great it seems to be working fine now using bob's code and calling the function from the query, after looking more closely one of the issues causing the #Error on some records was as bob suggested: the End date is < start date on some records.

Thanks a lot for everyones comments on getting this working

 

[hokiewalrus]

I've used the above function to try and do the same thing, count the number of days excluding weekends, however I've been getting inconsistent results, especially with small differences between the two dates.

If the 2 dates are the same, I'm still getting a 1 day difference and if they are 1 day apart I'm getting a 2 day difference. I thought it was just counting an extra day but that isn't consistently happening. If the two dates are 1 day apart, sometimes I get a 1 returned and sometimes a 2.

I admit I can't really follow the logic of the function, as such I'm not sure if it takes timestamps into account (which may explain the results I'm getting). I'm using the function completely unmodified.

See the attached screenshot for an illustration of my problem. Days in PL is the difference between FirstPLDate and LastPLDate and Days Till GL is the difference between LastPLDate and GLDate.

EDIT: Strike all that, I used the newer

Public Function fGetWorkdays2(pstart As Date, pend As Date) As Integer
'************************************************
'Purpose:   Compute number of workdays (Mon - Fri)
'           between pStart and pEnd
're:        http://www.access-programmers.co.uk/forums/showthread.php?t=164254&highlight=simplified
'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

And it works great, thanks!