By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,946 Members | 734 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,946 IT Pros & Developers. It's quick & easy.

Computing number of days between 2 dates not counting the weekends.

P: 6
Hi,
I'm quite new to the world of VBA so any help you could extend will help me a lot with my project.

I have a database of Change Requests from our users. I have two date boxes, one is Date Request Raised and Deadline for Entry. What I'd like to happen is when I enter the date on the Date Request Raised the Deadline for Entry would automatically be filled based on the Asset type that was selected.

For example:
if the asset type = 2 then the Deadline for Entry would only be a day from when the request was raised. If the asset type is <> 2 then the deadline for entry would be 5 working days from the day the request was raised where the weekend days are not counted.

I saw a module for just counting the weekdays but I just dont know where and how I can use the module with the DateAdd function for the situation i mentioned above.

If you can provide me with a solution and guide me as well as to where I should put the code in (i.e., Before Update, OnClick etc.), I'd really appreciate it.

Many thanks.
Sep 19 '08 #1
Share this Question
Share on Google+
2 Replies


FishVal
Expert 2.5K+
P: 2,653
Something like this:

Expand|Select|Wrap|Line Numbers
  1. Public Function WorkDays(dteStart, dteEnd, _
  2.                          Optional dowFirstDayOff As VbDayOfWeek = vbSaturday, _
  3.                          Optional lngDaysOffCount As Long = 2) As Long
  4.  
  5.     Dim lngDaysOff As Long
  6.     Dim dteFirstDayOff As Date
  7.  
  8.     'calculate total days inclusively
  9.     WorkDays = DateDiff("d", dteStart, dteEnd) + 1
  10.  
  11.  
  12.     'subtract days off count moving [dteStart] forward [lngDaysOffCount] times
  13.     For i = 0 To lngDaysOffCount - 1
  14.  
  15.         'find first day off date in the range
  16.         If Weekday(dteStart + i, dowFirstDayOff) > lngDaysOffCount Then
  17.             dteFirstDayOff = dteStart + i + 7 - Weekday(dteStart + i, dowFirstDayOff) + 1
  18.         Else
  19.             dteFirstDayOff = dteStart + i
  20.         End If
  21.  
  22.         'exit in a case [dteFirstDayOff] got out of the range
  23.         If dteFirstDayOff + i > dteEnd Then Exit Function
  24.         'calculate [dteFirstDayOff] weekdays in the range and subtract from the total days
  25.         WorkDays = WorkDays - DateDiff("w", dteFirstDayOff + i, dteEnd) - 1
  26.  
  27.     Next i
  28.  
  29. End Function
  30.  
Regards,
Fish
Sep 19 '08 #2

FishVal
Expert 2.5K+
P: 2,653
Bugfix:
Line #23 "+i" has to be removed.
Sep 19 '08 #3

Post your reply

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