473,890 Members | 1,397 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Is there a DateDiff function that counts business days?

Seth Schrock
2,965 Recognized Expert Specialist
I am trying to create a report that tells us how long it takes work to get between stages. Since we don't work on Sundays or federal holidays, I was hoping to omit those days from the count. We do work Saturdays so they would need to be included. Basically, I want a count of 1 if it left stage 1 on Saturday and left stage 2 on Monday. Is this possible, or do I just need to tell management that the count includes Sundays and holidays?
Dec 6 '12 #1
4 18942
5,501 Recognized Expert Moderator Expert
Counting the Number of Working Days in Access 2007
Dec 6 '12 #2
8,834 Recognized Expert Expert
I created a simple Function that will count the number of Workdays, including Saturdays, but not Sundays and Federal Holidays. Create a Table named tblHolidays with a single DATE/TIME Field named [Date]. Populate this Table with any and all work related Holidays, then execute the Code
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalcWorkingDays(dteStartDate As Date, dteEndDate As Date) As Integer
  2. Dim intCount As Integer
  4. intCount = 0
  6. Do While dteStartDate <= dteEndDate
  7.   Select Case Weekday(dteStartDate)
  8.     Case Is = 1
  9.       'It's a Sunday, do nothing
  10.     Case Is = 2, 3, 4, 5, 6, 7      'Monday thru Saturday
  11.       'is it a Holiday as posted in tblHolidays?
  12.       If DCount("*", "tblHolidays", "[Date] = #" & dteStartDate & "#") < 1 Then     'NOT Holiday
  13.         intCount = intCount + 1
  14.       End If
  15.   End Select
  16.     dteStartDate = dteStartDate + 1
  17. Loop
  18.   fCalcWorkingDays = intCount
  19. End Function
Executing fCalcWorkingDay s(#12/1/2012#,#12/31/2012#) will return the Value 25 since there are 5 Sundays in December as well as Christmas.
Dec 6 '12 #3
32,584 Recognized Expert Moderator MVP
Logically, Access cannot be programmed to know about federal holidays as holidays around the world cannot be accurately predicted in advance. They change for various reasons and Access would need to be able to handle all holidays for all countries if it were to offer that functionality. I wasn't involved in designing Access but I'd be prepared to bet that facility isn't available, as it wouldn't make sense.

That said, the number of days in a date range is pretty easily available, so starting with that is a decent basis to work from (in order to produce your own function to return this value). Knowing the number of days in the range and the start day of the week you can easily determine how many Sundays fall within that range. All that's left now is for the number of public holidays to be subtracted from the number. You have a date range, remember.

You now need to maintain a table of public holiday dates in your database (or even some central BE database that all your projects can share). With that available you can either look up all the values in the date range or, more efficiently, link that table into any query you're using to ensure the relevant dates are already available. Be careful of Sundays. They either need to be omitted completely from the table or the function will need to ignore them when it determines the public holidays to subtract.

So, this is certainly not as trivial as finding the number of days in a date range, but nor is it excessively complicated. It does, however, mean that there is a new responsibility of ensuring this new table is always up-to-date and adequately populated.
Dec 6 '12 #4
Seth Schrock
2,965 Recognized Expert Specialist
That is exactly what I was needing, but I certainly didn't expect it to be that complicated. Thankfully Copy/Paste worked pretty well. I just had to change the table name to fit my database, but other than that it worked perfectly.
Dec 17 '12 #5

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

Similar topics

by: Anthony Robinson | last post by:
Is anyone aware of a function (system or user defined) that will calculate business days? For instance: I have a column in as table called DATE. I want to be able to add five business days to that date and come up with a new date. Is that possible. Also, is there anyway that DB2 can be aware of holidays? Maybe load them onto the server in some type of reference file or something. I ask these questions because I'm working on a banking...
by: info | last post by:
Can some one show me how to use the datediff function where the dates are being supplied through an access db. For instance, a recordset would contain these fields: DateAssigned, DateDue, ProjectName, ProjectDetail, Class. The student enters a project name, then a date it was assigned and a date it is due...and other details about the project into the appropriate fields. The projects appear in a databound list box...when you click on a...
by: mcbill20 | last post by:
Hello all. I have a really basic question that I hope someone has a better answer for. I apologize in advance-- I know this is probably a really basic question but I am used to Oracle rathern than Access. I have a database where they customer wants to purge records from certain tables after three years. When I was asked to make the changes I originally thought this was an incredibly simple thing to do. I looked at the function list and...
by: CDMAPoster | last post by:
I'm starting to come up with a version of DateAdd that I call BusinessDateAdd that adds the selected number of business days. It's still in preliminary form (needs testing) and interacts with my direct date functions (which have also changed slightly). There's no room to put the code in the margins :-), but there's enough room to put up a few URL's. The zip file is a zipped A97 mdb file with a single module. Zipped: ...
by: Sam | last post by:
Hi, I use C# in my ASP.NET projects. Here's what I need to do: I want to add x business days to a given date i.e. add 12 business days to today's date. What is the best, fastest and most efficient way for me to do this? -- Thanks, Sam
by: DumStudent | last post by:
I need help with what to write for my DateDiff Function to work properly between two datetimepicker boxes to give 'days' results. This is what I have and it works except I can't have the intDate1 to be specific and intDate2 as Now. I need to make it work so when the second date is chosen it gives the days between current date in intDate1 and date selected in intDate2. Help Please?? Dim intDiff As Integer Dim intDate1 As...
by: KMEscherich | last post by:
Using Access '97 Hi there. I have a field that uses a DateDiff function to count the number of days between a DATE field and DATE(), is there a way to have the counter stop counting days when the FINISHED checkbox has been selected?? =DateDiff("d",,Date()) & " DAYS HAVE PASSED SINCE THE SRI SUBMITTED DATE" Thank you in advance for your assistance. K_Escherich
by: JenM | last post by:
Hi. I am a novice user. I am using the datediff function in a query. I am trying to calculate the number of week days between two dates. When I use "m" as the interval, the number of weeks is returned. When I use "d" or "y" as the interval, the number of days is returned. What kind of calculation can I add to the statement to only count the number of week days? Is this possible? I was thinking I might have to run a query on a query...
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
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 business days count which is defined as count of days (optionally inclusive in the current implementation) excluding weekend days and holidays. Let us say periods to calculate are stored in table associated with contacts. keyPeriodID -...
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: 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: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
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: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
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.