UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

3 Pages V  < 1 2 3  
Reply to this topicStart new topic
> Calculating between dates excluding weekends and holidays, Office 2007    
 
   
Yorky_North
post Feb 28 2012, 05:40 AM
Post #41

UtterAccess Enthusiast
Posts: 54



A more exact method
CODE
Function NumberOfWorkingDays(dteOne As Date, dteTwo As Date)
Dim intNoDays As Integer
Dim E As Integer
For E = 1 To DateDiff("d", dteOne, dteTwo)
    Select Case DatePart("w", DateAdd("d", E, dteOne))
        Case vbSaturday, vbSunday
        Case Else
            intNoDays = intNoDays + 1
    End Select
Next
NumberOfWorkingDays = intNoDays + 1
End Function
Go to the top of the page
 
+
Barbll
post Mar 22 2012, 02:16 PM
Post #42

UtterAccess Member
Posts: 36
From: Ontario, Canada



We have been successfully using this code on our databases. We have been using MS Office XP but are in the midst of converting to Office 2010. I have been trying to solve a problem with one of two files that no longer exist with Office 2010 and that is ACEDAO.DLL. I've adjusted input masks for date format to be 99/99/00;0;0;/
When we open it we get the error message that there is a "missing or broken reference to ACEDAO.DLL ver 12.0. Then the Visual Basic Editor opens with the ISO_WorkdayDiff code and highlights on the Format area:

If booExcludeHolidays And lngDays > 0 Then
strDateFrom = Format(datDateFrom, "yyyy\/mm\/dd")
strDateTo = Format(datDateTo, "yyyy\/mm\/dd")
strFilter = cstrFieldHoliday & " Between #" & strDateFrom & "# And #" & strDateTo & "# And Weekday(" & cstrFieldHoliday & ", 2) <= " & cbytWorkdaysOfWeek & ""
lngHolidays = DCount("*", cstrTableHoliday, strFilter)
End If

ISO_WorkdayDiff = lngDays - lngHolidays
End Function
Can you advise if I should be changing the date format in this code to prevent this error from happening? I don't know VBA so I'm not sure if that is what's causing the problem.
Any help would be awesome. Thank you.
Go to the top of the page
 
+
Gustav
post Mar 23 2012, 04:45 AM
Post #43

UtterAccess VIP
Posts: 2,163



Not a code problem.
On the code editor, go to menu Tools, References and sort out the reference marked MISSING.
/gustav
Go to the top of the page
 
+
Ray
post Jul 14 2012, 04:14 AM
Post #44

UtterAccess Veteran
Posts: 444



Gustav,
The code works very good and easily for an elementary level of Access knowledge like me. It also attracts me to continue learning it. I encounter a minor issue. If either BegDate or EndDate, or both are Null, return a "#Error". How can I get a blank cell (stop calculation) like the result of direct substraction [EndDate]-[BegDate]? Thanks!
Go to the top of the page
 
+
Gustav
post Jul 14 2012, 08:39 AM
Post #45

UtterAccess VIP
Posts: 2,163



If either or both dates can be Null, you can modify this:
ISO_WorkdayDiff([StartDate],Nz([EndDate],[StartDate]),True)
to:
=IIf(IsNull([StartDate]+[EndDate]),Null,ISO_WorkdayDiff([StartDate],[EndDate],Tr
ue)
/gustav
Go to the top of the page
 
+
Ray
post Jul 14 2012, 10:04 AM
Post #46

UtterAccess Veteran
Posts: 444



Gustav,
Thanks for your advice. I change the statement in query to Leadtime: IIf(IsNull([StartDate]+[EndDate]), Null, ISO_WorkdayDiff([StartDate], [EndDate], True)+1) and works as per my preference.
Go to the top of the page
 
+
Gustav
post Jul 14 2012, 11:34 AM
Post #47

UtterAccess VIP
Posts: 2,163



OK, great!
gustav
Go to the top of the page
 
+
Ray
post Jul 15 2012, 03:57 AM
Post #48

UtterAccess Veteran
Posts: 444



Can this function help to give the net workdays when we input month and year?
Go to the top of the page
 
+
Gustav
post Jul 16 2012, 05:20 AM
Post #49

UtterAccess VIP
Posts: 2,163



Yes, use:
ateSerial(inputYear, inputMonth, 1)
for the start date and:
DateSerial(inputYear, inputMonth + 1, 1)
for the end date.
/gustav
Go to the top of the page
 
+

3 Pages V  < 1 2 3
Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 31st October 2014 - 12:02 PM