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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V < 1 2 3  (Go to first unread post)
   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



Posts: 54
Joined: 4-May 11



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



Posts: 36
Joined: 13-January 11
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
Joined: 21-February 07



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



Posts: 521
Joined: 31-January 00



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
Joined: 21-February 07



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



Posts: 521
Joined: 31-January 00



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
Joined: 21-February 07



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



Posts: 521
Joined: 31-January 00



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
Joined: 21-February 07



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


Custom Search
RSSSearch   Top   Lo-Fi    4th August 2015 - 12:51 PM