By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
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

ddtpmyra
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
Share this Question
Share on Google+
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
  1. SELECT Count(tblDiary.DiaryDate) AS CountOfDiaryDate
  2. FROM tblDiary
  3. 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
  1. Private Function DiffJour(ByVal DateDeb As Date, ByVal DateFin As Date) As Integer
  2.  
  3. ' Calculates number of days betwwen two dates excluding saturdays and sundays
  4.  
  5. ' DiffJour = (DateFin - DateDeb) - (saturdays + sundays).
  6.  
  7.     On Error GoTo Err_DiffJour
  8.  
  9.     Dim DiffJr As Integer
  10.  
  11. ' Base difference
  12.     DiffJr = DateDiff("d", DateDeb, DateFin)
  13.  
  14. ' Eliminate saturdays and sundays
  15.     Do While DateDeb <= DateFin
  16.         If Weekday(DateDeb, 7) <= 2 Then
  17.             DiffJr = DiffJr - 1
  18.         End If
  19.         DateDeb = DateDeb + 1
  20.     Loop
  21.  
  22. ' In case there is no working day between the two dates.
  23.     If DiffJr < 0 Then
  24.         DiffJr = 0
  25.     End If
  26.  
  27.     DiffJour = DiffJr
  28.  
  29.     Exit Function
  30.  
  31. Err_DiffJour:
  32.     MsgBox Err.Description
  33. End Function
Jul 29 '08 #3

ddtpmyra
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

NeoPa
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

Post your reply

Sign in to post your reply or Sign up for a free account.