473,322 Members | 1,719 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.

With a table of holidays and A97's date fn's - how best to count weekends and holidays between two dates

MLH
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
Sep 23 '06 #1
2 3107
MLH
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

Sep 23 '06 #2
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

Sep 25 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
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...
17
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...
1
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...
2
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...
3
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 -...
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
2
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...
1
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...
0
by: geraldjr30 | last post by:
hi, i have the following: <html> <STYLE type="text/css"> TD{font-family:ARIAL;font-size:11px;color:#666666;}
0
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...
0
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...
0
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...
0
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...
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: 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.