473,881 Members | 1,704 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to calculate a date that is x weekdays from a start date?

2 New Member
I had no experience in VB before writing this so bare with me...
the function below seems to work for certain cases, but not all (in some cases it is a week behind). Can anybody help me figure this out? It is supposed to take an integer(numDays ) and a date and calculate the date that is "numDays" weekdays from the start date. I don't know if it is the if statement at the bottom or the particular cases that seem not to work.

Here it is it seems to work for (days) cases 0, 3 and 4, but not for 1 or 2:

Expand|Select|Wrap|Line Numbers
  1. ' Accepts a start date and an integer(numDays) and calculates the date that is "numDays"
  2. ' number of work(week) days after the start date.
  4. Public Function Workdays(ByRef startDate As Date, ByRef numDays As Integer) As Date
  6. Dim weekHold As Integer
  7. Dim dateHold As Date
  8. Dim dayHold As Integer
  10. Dim days As Integer
  11. days = numDays Mod 5
  12. dayHold = days
  14. ' if due date will fall on a sunday, advance two days.
  15. Select Case days
  16. ' when you add 1 day and x weeks to the startDate to get the due date
  17.     Case 1
  18.       Select Case DatePart("w", startDate) + days
  19.       ' sunday and monday
  20.         Case 1, 2
  21.             dayHold = days + 1
  22.         ' saturday
  23.         Case 7
  24.             dayHold = days + 2
  25.         ' else
  26.         Case Else
  27.             dayHold = days
  28.         End Select
  29. ' when you add 2 days and x weeks to the startDate to get the due date
  30.     Case 2
  31.         Select Case DatePart("w", startDate) + days
  32.         ' sunday, monday and saturday
  33.             Case 1, 2, 7
  34.                 dayHold = days + 2
  35.         ' tuesday
  36.             Case 3
  37.                 dayHold = days + 1
  38.             Case Else
  39.                 dayHold = days
  40.             End Select
  41. ' when you add 3 days and x weeks to the startDate to get the due date
  42.     Case 3
  43.         Select Case DatePart("w", startDate) + days
  44.         ' wednesday
  45.             Case 4
  46.                 dayHold = days + 1
  47.         ' Thursday and friday
  48.             Case 5, 6
  49.                 dayHold = days
  50.             Case Else
  51.                 dayHold = days + 2
  52.             End Select
  53. ' when you add 4 days and x weeks to the startDate to get the due date
  54.     Case 4
  55.         Select Case DatePart("w", startDate) + days
  56. ' wednesday
  57.             Case 4
  58.                 dayHold = days + 1
  59. ' thursday and friday
  60.             Case 5, 6
  61.                 dayHold = days
  62.             Case Else
  63.                 dayHold = days + 2
  64.             End Select
  65. ' only case left is case where you just add a number of weeks to the startDate to get due date.
  66.     Case 0
  67.         dayHold = 7
  68. End Select
  70. 'calculate due date
  71. weekHold = numDays / 5
  72. If weekHold > 0 Then
  73.     weekHold = weekHold - 1
  74. End If
  76. dateHold = DateAdd("ww", weekHold, startDate)
  77. dateHold = DateAdd("d", dayHold, dateHold)
  78. Workdays = dateHold
  80. End Function
Dec 13 '10 #1
3 2731
8,834 Recognized Expert Expert
Unless I am totally misinterpreting this Thread, to calculate a Date that is numDays of Weekdays from a startDate and return a Date Value:
Expand|Select|Wrap|Line Numbers
  1. Public Function Workdays(ByRef startDate As Date, ByRef numDays As Integer) As Date
  2. Dim intWkDate As Integer            'Will track the Number of Weekdays accumulated
  3. Dim dteDate As Date
  4. Dim intDayCtr As Integer            'Will track Overall Days
  6. If numDays = 0 Then Exit Function
  8. Do
  9.  intDayCtr = intDayCtr + 1                          'Holds overall Date Increments
  10.   dteDate = DateAdd("d", intDayCtr, startDate)
  11.     If Not Weekday(dteDate) Mod 7 < 2 Then          'Yep it is a Weekday, increment Weekday Counter
  12.       intWkDay = intWkDay + 1
  13.     End If
  14. Loop Until intWkDay = numDays                       'Is Number of weekdays = numDays?
  15. 'When you get here, the Number of Workdays will equal the numDays Value. The
  16. 'actual Date has been sequentially stored in dteDate
  17. Workdays = dteDate
  18. End Function
Dec 14 '10 #2
Jared Elyea
2 New Member
This looks a little nicer than mine! (Okay, a lot). Did you have any idea where I went wrong (aside from not looping and saving a lot of work). If you don't care to analyze it, that is okay. I was just wondering if you noticed anything for the sake of learning from my mistakes.

Thanks a million for your help
Dec 14 '10 #3
8,834 Recognized Expert Expert
I think that it was simply a matter of over-complicating the problem and arriving at the incorrect Logic. That will come with experience.
Dec 14 '10 #4

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

Similar topics

by: Matt | last post by:
My ASP page allows the user select the date, and it will display the report during that week of that date. My question is when the program query database, I need to know the beginning date and ending date of that week. Any ideas??
by: sandy | last post by:
Hi, Using Java script I am trying to create code where when you place in the start date it automatically calculates 6 months for the experations date. For example when I place 01/01/04 as the issue date the experation date should automaically generate as 06/01/04. I would appreciate it if anyone could help me. Thank you Sandy
by: Dennis M. Marks | last post by:
What is the maximum valid date range for the date(yyyy,mm,dd) function. -- Dennis M. Marks http://www.dcs-chico.com/~denmarks/ Replace domain.invalid with dcsi.net -----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
by: David Kuhn | last post by:
I have a query with a date field criteria of: Between And When the query is run, I am asked for the Start date and then the End Date. So far, so good. The records returned are all those in between those dates and not records that are on the start or the end date. For example, I enter "09/01/03" for the start date and "09/30/03" for
by: CheezIt2831 | last post by:
hello, I have 5 tables(Alpha, Bravo, Charlie, Service, Trans). What i am trying to do is be able to select a start date and ending date for a report and include fields from the 5 tables. this is my code: SELECT , , , , FROM UNION SELECT , , , , FROM UNION SELECT , , , , FROM UNION SELECT , , , , FROM
by: Scott | last post by:
I have a report that summarizes attendance occurances for my team of 50 employees. Right now the date values are entered by the user before report execution. I want the start date to be flexible... I built a query which shows all reps on an active attendance write up (within the last 180 days). I want the attendance report to use the query's write up date as the start date for each employee (if an active write up exists), and if no...
by: gpspocket | last post by:
help me -CURSOR backward insert from End Date > to Start Date how to insert dates from end to start like this SELECT 111111,1,CONVERT(DATETIME, '17/03/2008', 103), CONVERT(DATETIME, '01/03/2008' i explain i have stord prosege that create mod cycle shift pattern and it working ok
by: Del | last post by:
I have a parameter query that requires the user to enter a Start Date: and End Date: and pull data between that date range. I am currently using the following parameter; Select * From mytable Where between and This query will always be run on a Monday and the start date will always be the previous Sunday. The end date will be the previous
by: Connie via AccessMonster.com | last post by:
Hi Access Building Friends, I am building a database for a manufacturer who needs to know the projected End_Date of each job. I know the Start_Date and the total days required to do the job. I need to calculate the End_Date after taking into account Weekends and Holidays.
by: =?Utf-8?B?QW5pdGhh?= | last post by:
Hi, Currently for Announcement library a custom column "publish start date" is added and under the search result webpart changed the xslt by filtering the result based on the publish start date .Publish start date is not mandatory. if the publish start date <"" and publish start date <= today then the item get displayed under the search results else the item won't get displayed.
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.