Working Days (Access 97)

Topic

New Reply

Hi

On reviewing the solutions and other websites (such as http://www.mvps.org/access/index.html) there always seems a problem with catering for holidays. Specifically, for all the solutions catering for holidays you have to enter the specific holiday dates, and I got to thinking that there must a better way of doing this.

I have gotten 95% of the way, but as usual I have over engineered the code and there must be a simpler way. So I have attached the test DB for hopefully some friendly peer review and pointers on improving it. While there is probably a better solution from those more experienced than myself, it has been a good learning experience for me.

A few issues for comment:

1 It does not give a correct solution in some instances eg 17 Apr 03 plus 4 working days gives the wrong date.
2 It runs awfully slow for a big working day number.
3 I had troubles getting the dates to read correctly for checking, hence the temporary holiday table. I tried all sorts of solutions such as format, Cdate, dateserial and #, but could not get a consistent result.

Also a few pointers for assistance:

1 There are 3 tables for a) storing fixed holidays, storing Easter holidays (no real solution for this issue), and c) temp table for storing actual fixed holidays to be used in calculating working days
2 The relevant two forms

Reply | Quote

Replies

You wrote:
>>Specifically, for all the solutions catering for holidays you have to enter the specific holiday dates, and I got to thinking that there must a better way of doing this.<<

I not so sure. The advantage of having a holiday table in which you enter specific dates is that it is easily customizable for every situation. If you have code to automatically figure out things (like when is Thanksgiving, etc.), it becomes a problem when the holidays you expect to have aren't honored in a given situation. For example. in most states Good Friday is a holiday, but in North Carolina that was a workday and the Monday after Easter was a holiday! (at least it was when I lived there some years ago)

I can see having a routine to automatically populate the holiday table, this would allow users to delete/add holidays as needed.

Reply | Quote

What I would do is to create a dates table (let us call it tblDates) that would hold a range a range of dates that people are allowed to enter (this could be 1/1/2000 till 31/12/2009) and set the appropriate fields for Holidays and Special (for the 3rd Monday in a certain month). The table would have the following fields:

DateInTable this obviously holds the date
DayOfWeek this holds the number for day of week (eg. 1 for Sunday, 2 for Monday)
Holiday this is a yes/no field set to true if it

Reply | Quote

Don’t know if you have checked out my recent threads on the subject, but i’m probably doing a similar thing to you at the moment.

The code below is from a faq on the tek-tips forum.. ( i went there accidentally, honest charlotte! )

This only uses 1 table to record public holidays. If you are using this for non-attendance reports, i don’t see the requirement to record individual holiday days, because holidays are by default, permissable.

Public Function Deltadays(StartDate As Date, EndDate As Date, StDate As Variant, LcDate As Variant, disc As String)
Dim rstHolidays As Recordset
Dim Idx As Long
Dim MyDate As Date
Dim Numdays As Long
Dim strCriteria As String
Dim NumSgn As String * 1
Dim dbs As Database
Dim Beginning As Date, Ending As Date
Dim JoinDate As Date, LeaveDate As Date
Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset(“tblHolidays”, dbOpenDynaset)
NumSgn = Chr(35)
MyDate = Format(StartDate, “Short Date”)
For Idx = Beginning To Ending

Select Case (Weekday(MyDate))
Case Is = 1 ‘ Sunday
‘ do nothing
Case Is = 7 ‘ Saturday
‘ do nothing
Case Else ‘ Normal Workday
strCriteria = “[HoliDate] = ” & NumSgn & MyDate & NumSgn
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
Numdays = Numdays + 1
Else
‘ do nothing
End If
End Select

MyDate = DateAdd(“d”, 1, MyDate)
Next Idx

Deltadays = Numdays

End Function

Reply | Quote

With all due respect, the code you posted is not doing a similar thing at all. For a start WTH needs to add nn working days to a starting date, not calculate the working days between two dates.

What I was trying to show was that he could keep a table which shows holidays, shows the weekday and shows any specials that he cares to set.
With all that in place I just provided a query to select the Top nn (number of working days) records and then select the top 1 of those.

I will post the amended database if people require it.

Pat

Reply | Quote

Hi

Thanks for all the input.

Some further comments. I realise holidays will vary from region to region, thus the DB can only ever be set up for a region. In my case, because I work for a Government Depart we are subject to a law that specifies what are public holidays so this is not an issue for me. I will at some stage need to develop it for number of days between two working dates, but the first priority is to work out the end date based on working days.

I would be interested in seeing Pat’s amended DB.

Thanks again.

Regards
WTH

Reply | Quote

I have included your amended database and you will find that there are extra forms, like:
frm Dates which I use as a verification that my date calcs are working
frm Generate tblDates this generates an entry in tblDates for each date between a keyed in start and end date
frm Update tblDates from tbamonthdays this updates the tblDates table with those tbamonthdays entries where DayofWK is null. I haven’t worked out yet how to update the table for the other type of entries yet.

I have changed the form frmWorkingdays to show the calculated end date of the start date and working days that are entered. I hould have put an extra check in there to check if it can calculate the nn working days, in other words, have enough dates been generated in the table tblDates.

A couple of the queries are not used, eg. the qry Years and qry tblDates.

If you have any problem understanding this, just post.
HTH
Pat

Reply | Quote

Refer to post 127952. The email address in the attachment to that post has changed, but the code’s still valid and could be modified for NZ instead of USA. Also, here’s an algorithm I found for Easter that could be made a part of the code in the referenced post.

Algorithm from anonymous correspondent from New York to Nature in 1876

In the text below, / represents an integer division neglecting the remainder, while % is division keeping only the remainder (MOD). So 30/7=4 ,

and 30%7=2 .

a=year MOD 19
b=year/100
c=year MOD 100
d=b/4
e=b%4
f=(b+8)/25
g=(b-f+1)/3
h=(19*a+b-d-g+15)MOD 30
i=c/4
k=c%4
l=(32+2*e+2*i-h-k)%7
m=(a+11*h+22*l)/451
Easter Month =(h+l-7*m+114)/31 [3=March, 4=April]
p=(h+l-7*m+114)MOD 31
Easter Date=p+1 (date in Easter Month)

Reply | Quote

What happens if I live in other than the US, these holidays are not applicable.
Pat

Reply | Quote

In the code from the other posts, just change the Select Case Month(datCurrDate) to cover your country/region. For example, February would be changed like this for New Zealand:
Case 2
If Day(datCurrDate) = 6 Then ‘ For Waitangi Day
datChkDate = datCurrDate
End If
If datChkDate = datCurrDate Then booHol = True

Reply | Quote

So if you had a package that was used all over the world you would need different source code for each Country. This is one of the reasons that I put these details into a table.
Pat

Reply | Quote

Nifty algorithm.

Is the Easter Date mentioned the Sunday?
Pat

Reply | Quote

Hi

Thanks for all the advice (particularly from Pat for his time and effort on the DB). There is certainly plenty of food for thought for me on how to manage work days and holidays.

Cheers

WTH

Reply | Quote

If you are interested I have put the Easter calculations in as well, thanks to EnvoyPV. The Easter days sets the Holiday check field in the table, it could easily be changed to set the SpecialDay check field instead.

I noticed in your first post how slow you found your solution for a large number of working days.
Try my solution and see what difference there is between a small number of days to a large number of days.

I’ll post the latest DB if you like.

Pat

Reply | Quote

Hi Pat

Yes please.

Thanks again.

Regards
WTH

Reply | Quote

It’s a pleasure, this was a good challenge.
Pat

Reply | Quote

Yes, Pat, you’d have to change source code for each region OR add source for each region that you were going to distribute to and take region as input from some source. I chose the method I did because it was an “in-house” federal gov’t database. This way, the users and I never have to concern ourselves about updating any “holiday” table.

The specific application it’s used for is to schedule operations from month to month. Some ot these jobs are daily, or every Monday or the first working day of every month, and occasionally some are manually assigned – you get the idea. If a user selects a job to be run and accidentally clicks on a holiday date, they are prompted that they’ve chosen a holiday and that the date has been changed for them to “xdate”.

Reply | Quote

Another reason I use tables for these things is that I despise “hard coding”, that goes back a long way with me.
But obviously you are happy doing that and it serves you well.
But I guess if you are catering for people all around the world then a table approach is the way to go (my opinion anyway).

Pat

Reply | Quote

I have made a few changes since I last posted, they are:
frm Update tblDates from tbamonthdays this updates the tblDates table with those tbamonthdays entries where DayofWK is null. I have now worked out how to update the table for the other type of entries (eg. 3rd Monday of January).

I have changed the form frmWorkingdays to show the calculated end date of the start date and working days that are entered. I have put an extra check in there to see if it can calculate the nn working days, in other words, have enough dates been generated in the table tblDates.

A couple of the queries that were not used now are used, eg. the qry Years and qry tblDates.

I have included a new query that calculates the number of working days between 2 dates, it is called [qry Calc Working Days between 2 Dates]. This query uses the same table structure as the other does.

Pat

Reply | Quote

I created a program that calculates Federal Holidays for a Leave Record in Excel. the method for calculating holidays should be easily adaptable to Access; I did everything in “table” format since I don’t know Excel very well. It seems to work; no one’s complained in over 2 years, and I even use it myself.

Hope it helps,

Reply | Quote