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?
4 18913 zmbd 5,501
Expert Mod 4TB
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 - Public Function fCalcWorkingDays(dteStartDate As Date, dteEndDate As Date) As Integer
-
Dim intCount As Integer
-
-
intCount = 0
-
-
Do While dteStartDate <= dteEndDate
-
Select Case Weekday(dteStartDate)
-
Case Is = 1
-
'It's a Sunday, do nothing
-
Case Is = 2, 3, 4, 5, 6, 7 'Monday thru Saturday
-
'is it a Holiday as posted in tblHolidays?
-
If DCount("*", "tblHolidays", "[Date] = #" & dteStartDate & "#") < 1 Then 'NOT Holiday
-
intCount = intCount + 1
-
End If
-
End Select
-
dteStartDate = dteStartDate + 1
-
Loop
-
fCalcWorkingDays = intCount
-
End Function
Executing fCalcWorkingDays(#12/1/2012#,#12/31/2012#) will return the Value 25 since there are 5 Sundays in December as well as Christmas.
NeoPa 32,556
Expert Mod 16PB
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.
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.
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...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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,...
|
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...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
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...
|
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,...
|
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...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |