UtterAccess.com
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.

In 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: 409



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: 409



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: 409



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:

DateSerial(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: 20th April 2014 - 06:27 PM