473,322 Members | 1,736 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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
2 2818
FishVal
2,653 Expert 2GB
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
2,653 Expert 2GB
Bugfix:
Line #23 "+i" has to be removed.
Sep 19 '08 #3

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

Similar topics

4
by: John | last post by:
hey all..... alright, I am frusterated to the point of throwing my machine out the window (this board went down, trying to find stuff on google, this has been a nightmare) so I hope you guys can...
5
by: BlackFireNova | last post by:
I need to write a report in which one part shows a count of how many total records fall within the working days (Monday - Friday) inside of a (prompted) given date range, in a particular...
4
by: dhnriverside | last post by:
Hi I have a date in UK Format (dd/mm/yyyy). I want to get a series of days after that date, but NOT Saturday or Sunday For example, if the date is 13/12/2004 (next Monday) and I get the next 9...
29
by: james | last post by:
I have a problem that at first glance seems not that hard to figure out. But, so far, the answer has escaped me. I have an old database file that has the date(s) stored in it as number of days. An...
9
by: clintonb | last post by:
I'm looking for a way to calculate the number of days between two dates using standard C++ functions. Would it be as simple as just using the difftime() function and then dividing that result by...
4
by: =?Utf-8?B?UGF1bA==?= | last post by:
Hi, I have a web application that I need to add 3 days to the Now day, but need to make sure that I skip weekends and holidays. For example if Now is friday, 3 days + now should be tuesday,...
8
by: =?Utf-8?B?QWw=?= | last post by:
I am working in vb2005. how can I calculate business days (not including holidays and weekends) between 2 dates? thanks Al
7
by: Mike | last post by:
I have a routine that's calculating business days but its not counting the weekend days that are between the start date and end date. If my start date is 9/26/08 and my end date is 10/01/08, I...
5
FishVal
by: FishVal | last post by:
IMHO, the following is not a how-to-do instruction to solve a particular problem but more a concept-proof stuff demonstrating possibilities of SQL. So, let us say the problem is to calculate...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.