UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Calculate Working Days Minus Holidays    
 
   
Brandi
post Jan 8 2007, 03:19 PM
Post#1



Posts: 1,688
Joined: 24-June 04



I found the following code under topics in Utter Access.
I have created a tblHolidays and a form where I enter StartDate and EndDate.
I am not quite sure how to tie in the module to the form to get the result for the number of working days between my StartDate and EndDate. Can I create a third field on the form for the result and would an expression there refer to the module?

Thank you.
Brandi
Option Compare Database
Public Function WorkingDaysHol(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDaysHol
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDaysHol
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above
intCount = 0
Do While StartDate <= EndDate
rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If
StartDate = StartDate + 1
Loop
WorkingDaysHol = intCount
Exit_WorkingDaysHol:
Exit Function
Err_WorkingDaysHol:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDaysHol
End Select
End Function
Go to the top of the page
 
doctor9
post Jan 8 2007, 03:44 PM
Post#2


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Brandi,
Yi,
Your third textbox on your form (not actually a field, but a control) would have this for the Control Source:
=WorkingDaysHol(StartDate, EndDate)
Assuming you've named your first two textboxes "StartDate" and "EndDate". Keep in mind that it's not necessary for the textboxes on your form to have the same name that is used by the function for parameters. Your textboxes could easily be called txtDayHired and txtDayLaidOff, which would make the control source for your third textbox to be this instead:
=WorkingDaysHol(txtDayHired, txtDayLaidOff)
Hope this helps.
Dennis
Go to the top of the page
 
Brandi
post Jan 8 2007, 06:51 PM
Post#3



Posts: 1,688
Joined: 24-June 04



Yes. That is so easy. Thank you very much.
Go to the top of the page
 
doctor9
post Jan 9 2007, 09:37 AM
Post#4


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Glad to help!
Dennis
Go to the top of the page
 
Brandi
post Jan 9 2007, 12:48 PM
Post#5



Posts: 1,688
Joined: 24-June 04



I just wanted to verify something about the code above and how it is handling the holidays. If I enter a StartDate and EndDate, I know it will subtract out any Saturdays and Sundays and holidays. It looks to me like it is also checking to see if any of the holidays fall on a Saturday or Sunday too?
If a holiday is on a Saturday or Sunday is it not subtracting an extra day?
Thanks.
Brandi
Go to the top of the page
 
doctor9
post Jan 9 2007, 03:34 PM
Post#6


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Brandi,
You are correct in your logic, except there isn't really any subtraction happening. Here's how the code is written, in plain english:
If the current day is a weekday,
and today is not a holiday,
Then add one more day to the workday counter.
For instance, Easter is on a Sunday, so a task from Wednesday before Easter thru Tuesday after Easter would count as five days (Wed, Thu, Fri, Mon, Tue).
However, if your company takes the monday after Easter off, you'd include 4/9/07 in your tblHolidays, and the count would now be four days (Wed, Thu, Fri, Tue).
In other words, the person who wrote this function assumed that ALL Saturdays and Sundays are non-work days.
Dennis
Go to the top of the page
 
datAdrenaline
post Jan 9 2007, 03:37 PM
Post#7


UtterAccess Editor
Posts: 18,006
Joined: 4-December 03
From: Northern Virginia, USA


Here is a "Working Days" function from the code archive ... Its pretty efficient AND uses a table of holidays as well ... Just added it as an FYI ...

Net Working Days
Go to the top of the page
 
Brandi
post Jan 9 2007, 05:05 PM
Post#8



Posts: 1,688
Joined: 24-June 04



I didn't totally understand this code.
That is the *2 doing?
Ounderstand that 7 = Sunday and 6 = Saturday and <= 6 means a workday but what are all the ,1 and ,2 for?
The holiday lookup makes sense.
Thank you.
Brandi
Go to the top of the page
 
datAdrenaline
post Jan 10 2007, 11:44 PM
Post#9


UtterAccess Editor
Posts: 18,006
Joined: 4-December 03
From: Northern Virginia, USA


Weekday(#<some date>#, <vb constant to indicate what is the first day of the week>)
eekday(#1/10/2007#, vbSunday) = 4 ... The 4th day of the week, if the first day is Sunday
Weekday(#1/10/2007#, vbMonday) = 3 ... The 3rd day of the week, if the first day is Monday
{Note: vbSunday as the first day of the week is the default ...}
The results of Weekday represent the the day of the week IF the first day is as indicated, so the results do NOT necessarily correspond to the vb constants of:
vbSunday = 1
vbMonday = 2
vbTuesday = 3
vbWednesday = 4
vbThurday = 5
vbFriday = 6
vbSaturday = 7
Also, in the code the author has used explicit numbers instead of visual basic constants ... so to re-write it in familiar verbage:
CODE
Public Function gfncNetWorkdays(pdatStart As Date, pdatEnd As Date) As Long
  gfncNetWorkdays = DateDiff("d", pdatStart, pdatEnd) - (DateDiff("ww", pdatStart, pdatEnd, vbSunday) * 2) - _
                    IIf(Weekday(pdatEnd, vbSunday) = 7, IIf(Weekday(pdatStart, vbSunday) = 7, 0, 1), IIf(Weekday(pdatStart, vbSunday) = 7, -1, 0)) + _
                    IIf(Weekday(IIf(pdatStart <= pdatEnd, pdatStart, pdatEnd), vbMonday) < 6, IIf(pdatStart <= pdatEnd, 1, -1), 0) - _
                    (Nz(DCount("*", "tblHolidays", "HolidayDate Between #" & pdatStart & "# and  #" & pdatEnd & "# AND Weekday(HolidayDate,2) < 6"), 0) * _
                     IIf(pdatStart <= pdatEnd, 1, -1))
End Function

The basic premise of the code is this:
- Get the number of days
- Subtract the number of Sundays and Saturdays {DateDiff("ww", ....) returns the number of Sundays, so the "*2" is to "guess" at the number of Sundays PLUS the number of Saturdays}
- Make adjusts to the Saturdays and add/subtract accordingly
- Subtract the number of Holidays
Also .. for a "learning excersise", I created my own NetWorkDays, which I find easier to read:
Net Work Days
Go to the top of the page
 
doctor9
post Jan 11 2007, 09:57 AM
Post#10


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


This is why it's so important to include comments in code you're using. It may make perfect sense to you at the time you're writing the code, but three years down the line you'll look at it and wonder what the heck you were thinking.
Personally, I think the code in the original post works just fine. At least with that one, it's easier to visually trace the code to see how it works, even if it doesn't have any comments.
Dennis
Go to the top of the page
 
datAdrenaline
post Jan 11 2007, 12:32 PM
Post#11


UtterAccess Editor
Posts: 18,006
Joined: 4-December 03
From: Northern Virginia, USA


...Yes ... Comments are good/great ... but by the same token "Self Documenting Code" should also be a goal to strive towards.... Like naming variables to meaningful names (ie: "lngPlanID" instead of "z"); using good formating (ie: proper indentation!!) ... The "complication" of the function in the code archive by schroep is what lead me to create one of my own (there is a link to it in my previous post) ... just so I could read it!!! sad.gif. I had been using the schroep code "blindly" {just used it, but didn't need to know how it worked!} ... but Brandi's inquiries lead me to delve into it deeper!!

Aside from all this, the ORIGINAL function (Arvin Meyers) will return the intended result, the thing I don't like about it is the searching the recordset for EACH day of your date range. It is way more efficient to use standard functions.

HAs a "play time" activity ... I put the three functions in question (ArvinMeyer, Schroep, & datAdrenaline) in a module and did a simple speed test ... I executed each function 50 times to calc the networkdays between 1/1/2006 and 12/31/2006. My tblHolidays table has 35 records in it, 16 of those records are for 2006, which is the date range in question. Please note that as the years go by, EACH funtion will be adversely affected by the size of the tblHolidays table, but Arvins will be affected the most.

THere are the results ...

Test Conditions:
- Each function executed 50 times
- Timed with GetTickCount API call
- Results are in milliseconds.
- UNCOMMENTED the Startdate = Startdate + 1 line in Arvins code
- PC Specs: P4 2.8GHZ, 512 RAM, a couple of apps open in the background

{Note: All functions returned a same value for net work days (244 with my data)}
CODE
Pass 1
--------------------
ArvinMeyer:    1251
Schroep:       331
datAdrenaline: 230
Pass 2
---------------------
ArvinMeyer:    1262
Schroep:       300
datAdrenaline: 230
Pass 3
---------------------
ArvinMeyer:    1272
Schroep:       300
datAdrenaline: 230


The function I wrote as a learning exercise turned out to be the most efficient. But the intent of the test was to show the inefficiency of looping, which I think this small test does. Basically, looping was about 4 to 5 times slower than standard functions (ArvinMeyer/datAdrenaline = 5.5 from pass 3).

So ... ultimately, I have ditched Schroeps code for my code ... and I think my code is fairly easy to read.... BUT ... I wrote it!! dazed.gif ... Does it make sense to you??
Go to the top of the page
 
Brandi
post Jan 11 2007, 12:35 PM
Post#12



Posts: 1,688
Joined: 24-June 04



Thank you all. I was able to make the first function work for my purposes but wanted to understand the second one too. This will be very helpful to me in a couple of different situations. If there are awards for forums, Utter Access gets my vote.
Brandi
Go to the top of the page
 
Brandi
post Jan 11 2007, 12:52 PM
Post#13



Posts: 1,688
Joined: 24-June 04



I also now see the third version and I am able to understand what it does, so if it is indeed more efficient than looping then I will try to apply that to my database.
Thanks again to everyone.
Brandi
Go to the top of the page
 
doctor9
post Jan 11 2007, 02:07 PM
Post#14


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Brandi,
Glad to offer a little assistance!
Brent,
I must admit that your previous explanation of the use of the "* 2" bit helped a lot towards understanding it better. It probably would've taken some serious playtesting time before I would've figured that part out.
So, just for future use, I've copied & pasted it into my database of "useful code ideas", with some extra comments at the top to remind me of how it works.
Dennis
Go to the top of the page
 
datAdrenaline
post Jan 11 2007, 02:14 PM
Post#15


UtterAccess Editor
Posts: 18,006
Joined: 4-December 03
From: Northern Virginia, USA


... I discovered something!!!! ....
IF the date range is SMALL the looping routine is FASTEST (which was a surprize to me ... the that was the facts!) ... but it is also the most effected by the date range. A Date range of about 15 days is the point of equality between, my code (datAdrenaline) and Arvins code, beyond the 15 days, mine is much faster. However, as the date range increases mine (and schroep's) stays consistent with respect to performance where as Arvins continually slows down.
Oplan to post my code to the code archive ... fully commented due to Doctor9!!! sad.gif
See you on UA!!
Go to the top of the page
 
Brandi
post Jan 11 2007, 02:33 PM
Post#16



Posts: 1,688
Joined: 24-June 04



Brent,
Oused your code and it is working fine. My only question is what is it doing if a holiday date also happens to be a Saturday or Sunday? Is it taking that day away twice - once as a regular sat or sun and once more if it is in the holiday table?
Brandi
Go to the top of the page
 
datAdrenaline
post Jan 11 2007, 03:07 PM
Post#17


UtterAccess Editor
Posts: 18,006
Joined: 4-December 03
From: Northern Virginia, USA


Yes ... it will ... but I do not beleive I would put Saturdays and Sundays in the holiday calender because, by convention, Saturdays and Sundays are already NON-"work" days, so no need to put then in the holiday calender. However, if your own circumstance deviates from the "standard" of what a "work" day is, then I would suggest you record ALL non-working days in tblHolidays (possibly change the name to tblNonWorkdays) then get a "raw" day difference and subtract out the count from tblNonWorkdays that are between the two dates in question.

In the event that an entry is made into tblHoldays that IS a Saturday or Sunday, here is some modified code to account for that possible anomoly.

CODE
Public Function fNetWorkdays(dtStartDate As Date, dtEndDate As Date) As Long
Returns the number of workdays between the two passed dates.  Saturdays and
'Sundays are NOT considered workdays.  Plus there is an assumption that a
'table exists that is named tblHolidays that identifies EACH holiday date
'in a field named HolidayDate.
'''''''''''''''''''''''''
'Author: Brent Spaulding
I'Version: 2
'Date: 1/11/2007
''''''''''''''''''''
    
    Dim lngDays As Long
    Dim lngSaturdays As Long
    Dim lngSundays As Long
    Dim lngHolidays As Long
    Dim lngAdjustment As Long
    
    'Count the number of RAW days between the dates
    lngDays = DateDiff("d", dtStartDate, dtEndDate)
    
    'Count the number of Sundays between the two days. Note the use of "ww" as
    'the date interval which forces the count of sundays
    lngSundays = DateDiff("ww", dtStartDate, dtEndDate, vbSunday)
    
    'Count the number of Saturdays between the two dates.  Note the use of "w" as
    'the date interval which will count the <day of first date in DateDiff()>.
    'So, to count the Saturdays, I adjust the start date of the datediff function
    'to the saturday BEFORE the dtStartDate of the passed range, thus the number
    'of Saturdays between the passed range is returned.
    lngSaturdays = DateDiff("w", IIf(Weekday(dtStartDate, vbSunday) = vbSaturday, _
                                    dtStartDate, _
                                    dtStartDate - Weekday(dtStartDate, vbSunday)), _
                                 dtEndDate)
    
    'Count the number of holidays
    lngHolidays = Nz(DCount("*", "tblHolidays", _
                         "HolidayDate Between #" & dtStartDate & _
                                      "# And #" & dtEndDate & "# And " & _
                         " Weekday(HolidayDate, 1) Not In (1,7)"), 0)
    
    'Make an adjustment based on whether or not the start date is a Saturday
    If Weekday(dtStartDate, vbSunday) = vbSunday Or Weekday(dtStartDate, vbSunday) = vbSaturday Then
        lngAdjustment = 0
    Else
        lngAdjustment = 1
    End If
    
    'Return the result
    fNetWorkdays = lngDays - lngSundays - lngSaturdays - lngHolidays + lngAdjustment
    
End Function


HTH ...

EDITS ADDED >>>
Corrected a bug in the IN clause ...
Edited by: datAdrenaline on Thu Jan 11 15:14:51 EST 2007.
Go to the top of the page
 
Brandi
post Jan 11 2007, 03:42 PM
Post#18



Posts: 1,688
Joined: 24-June 04



Thanks. I was thinking that standard holidays like Jan. 1 and July 4 and Dec 25 would be in the holiday table every year but in certain years any of these could actually be falling on a Sat or Sun.
So now, if someone chooses to put holidays in that fall on sat or Sun the function will work either way.
Go to the top of the page
 
ScottGem
post Jan 11 2007, 03:49 PM
Post#19


UtterAccess VIP / UA Clown
Posts: 32,217
Joined: 21-January 04
From: LI, NY


>
Right!, Holidays can vary from year to year. Using a Holidays table insures accuracy. Generally I keep 3-4 years worth in my Holidays table. Not usually necessary to keep more.
Go to the top of the page
 
doctor9
post Jan 12 2007, 04:59 PM
Post#20


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Brent,
ice internal documentation. Just curious, how's the speed comparison with your changes?
Dennis
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    15th November 2019 - 05:51 AM