434,806 Members | 1,506 Online
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

# datediff less the weekends

 100+ P: 333 I have below query inside the ms access, but this query counts all the days including the weekend but I only wanted to count the NUMBER of weekday (Monday to Friday). Please help Number of Days Open: DateDiff('d',[dbo_Ticket]![date_opened],Date()) I tried to us the 'w' and 'ww' but it counts the number week not the number of days in a week. Number of Days Open: DateDiff('w',[dbo_Ticket]![date_opened],Date()) Thanks! Jul 28 '08 #1
4 Replies

 P: 69 You will probably best meet this need by writing VBA code to test the day of the week for each date between the startdate andthe enddate and incrementing a counter if the day of the week is between Monday and Friday. You also could do this in SQL using a "WHERE" clause and the COUNT option: Expand|Select|Wrap|Line Numbers SELECT Count(tblDiary.DiaryDate) AS CountOfDiaryDate FROM tblDiary WHERE (((DatePart("w",[DiaryDate])) Between 1 And 5)); The benefit of the VBA alternative is that you could adjust it to exclude holidays Jul 29 '08 #2

 P: 1 Here is a function that sould do the job. Expand|Select|Wrap|Line Numbers Private Function DiffJour(ByVal DateDeb As Date, ByVal DateFin As Date) As Integer   ' Calculates number of days betwwen two dates excluding saturdays and sundays   ' DiffJour = (DateFin - DateDeb) - (saturdays + sundays).       On Error GoTo Err_DiffJour       Dim DiffJr As Integer   ' Base difference     DiffJr = DateDiff("d", DateDeb, DateFin)   ' Eliminate saturdays and sundays     Do While DateDeb <= DateFin         If Weekday(DateDeb, 7) <= 2 Then             DiffJr = DiffJr - 1         End If         DateDeb = DateDeb + 1     Loop   ' In case there is no working day between the two dates.     If DiffJr < 0 Then         DiffJr = 0     End If       DiffJour = DiffJr       Exit Function   Err_DiffJour:     MsgBox Err.Description End Function Jul 29 '08 #3

 100+ P: 333 To Panado: Need more help Im a newbie using the access. My question is... If i'll do this function inside access how can I apply or call this function is it on load of a report? thanks, Myra Jul 29 '08 #4

 Expert Mod 15k+ P: 31,489 I would suggest using modular arithmetic for the number of whole weeks, then using Weekday() determine which working days are between the two weekdays. Aug 5 '08 #5