With a table of holidays and A97's date fn's - how best to count
weekends and holidays between two dates? My holiday table
has 4 fields. I will be adding records to it each year as info
becomes known.
What approach would you advise to tally up the number
of holidays, saturdays and sundays between any 2 dates
in the range of my records in this table?
HoliDate HolEvent HoliWho HoliWeekDay
1/2/2006 New Year's Day Both Monday
1/16/2006 Martin Luther King, Jr. Birthday Both Monday
2/20/2006 Washington's Birthday Feds Monday
4/14/2006 Good Friday State Friday
5/29/2006 Memorial Day Both Monday
7/4/2006 Independence Day Both Tuesday
9/4/2006 Labor Day Both Monday
10/9/2006 Columbus Day Feds Monday
11/10/2006 Veterans Day Both Friday
11/23/2006 Thanksgiving Day State Thursday
11/24/2006 Thanksgiving Both Friday
12/25/2006 Christmas Day Both Monday
12/26/2006 Christmas State Tuesday
1/1/2007 New Year's Day Both Monday
1/15/2007 Martin Luther King, Jr. Birthday Both Monday
2/19/2007 Washington's Birthday Feds Monday
4/6/2007 Good Friday State Friday
5/28/2007 Memorial Day Both Monday
7/4/2007 Independence Day Both Wednesday
9/3/2007 Labor Day Both Monday
10/8/2007 Columbus Day Feds Monday
11/12/2007 Veterans Day Both Monday
11/22/2007 Thanksgiving Day Both Thursday
11/23/2007 Thanksgiving State Friday
12/24/2007 Christmas State Monday
12/25/2007 Christmas Day Both Tuesday
12/26/2007 Christmas State Wednesday 2 3107
I found the following in my own search. It seems to work.
It returns workdays. All I have to do get weekend and
holidays is subtract the 10 workdays from the 16 days
under consideration, leaving 6 weekend/holiday days.
Unless anyone can recommend something simpleer, (or
see any flaw in this logic), I'll stick wiith it.
?Workdays(#3/31/2006#, #4/15/2006#)
10
Option Compare Database
Option Explicit
Public Function Workdays(BeginDate, EndDate) As Integer
'Calculate the number of working days between two dates ..
' .. not counting weekend days
' .. and not counting holidays (listed manually in "Holidays" table).
Dim Interim, BD, ED, Diff As Integer
'Check for a NULL entry, which would give runtime error.
'------------------------------------------------------
If IsNull(BeginDate) Or IsNull(EndDate) Then
Exit Function
'This returns "0", so may want to trap IsNull
'and set Result = NULL on calling the function.
End If
'Calc weekdays between the dates (formula modified from A.R. 8/89)
'-----------------------------------------------------------------
BD = (BeginDate Mod 7) - 2
If BD < 0 Then BD = 0
ED = (EndDate Mod 7) - 1
If ED < 0 Then ED = 0
Interim = (Int(EndDate / 7) - Int(BeginDate / 7)) * 5
Diff = Interim - BD + ED
'Subtract holidays (from the Holiday table), if the holiday is between
' the dates and is not a weekend day.
' (proc modified from Lima - Dvlpg Pdox4 Applns 93)
'----------------------------------------------------------------------
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblHolidays")
rst.MoveFirst
Do Until rst.EOF
'Join 2 lines below to 1 and remove '_' character
If (rst!HoliDate >= BeginDate) And (rst!HoliDate <= EndDate) _
And ((WeekDay(rst!HoliDate - 1)) Mod 6 <0) Then
Diff = Diff - 1
End If
rst.MoveNext
Loop
rst.Close
Set dbs = Nothing
Workdays = Diff 'Return value to the function
End Function
An alternative:
1) Create table of holidays that would be workdays. (Example: If
Christmas is on sunday put the 26th as the holiday instead of the 25th)
And then use following:
'--------------------------------------------------------------------------*-------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding
Saturdays,
' : Sundays, and any days in the Holidays table
'--------------------------------------------------------------------------*-------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
On Error GoTo CalcWorkDays_Error
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate]
between
#" _
& dtmStart & "# And #" & dtmEnd & "#")
CalcWorkDays_Exit:
On Error Resume Next
Exit Function
CalcWorkDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit
End Function
Ron This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Dixie |
last post by:
I am trying to calculate the number of workdays between two dates with
regards to holidays as well. I have used Arvin Meyer's code on the Access
Web, but as I am in Australia and my date format is...
|
by: Remington |
last post by:
Back again, thirsting for knowlege. :)
http://www.thescripts.com/forum/showthread.php?p=2314976#post2314976
Was my last thread. I am still working on the same project, but a different stage.
I...
|
by: Rumple517 |
last post by:
In my Access database project, I need to determine deadline dates based on working days and excluding holidays. I have set up the table as instructed below. The code that was given is for...
|
by: Dan2kx |
last post by:
Hello
i have a holiday table with staff ID's and single entry dates (for holidays skipping weekends)
I need a crosstab to show the dates as the column, and the staff id as the row, and count...
|
by: Jim |
last post by:
Guys I'm rusty. Haven't messed with Access in a while.
I'm trying to determine how many workdays (or weekdays) between two dates.
I don't think their are any "canned" functions within access -...
|
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: angi35 |
last post by:
Hi,
I'm working in Access 2000. I have a form with a series of date fields, showing the progress of a project from start to completion. There's a set of fields/controls for projected dates (when...
|
by: trixxnixon |
last post by:
This code omits weekends, holidays and returns a due date as well as the number of business days due. it also adds an extra day to the reqest if it was submitted after 5:00.
as you can see that...
|
by: geraldjr30 |
last post by:
hi,
i have the following:
<html>
<STYLE type="text/css">
TD{font-family:ARIAL;font-size:11px;color:#666666;}
|
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...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
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)...
|
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...
|
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....
|
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
|
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...
| |