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    
 
   
Fresh
post Jul 5 2010, 12:43 PM
Post#1



Posts: 4
Joined: 30-June 10
From: Canada


Hello,
I am very, very new to Access 2007. While I am able to create tables to contain information, as well as basic queries, reports and forms, I lack the expertise to create more complicated expressions or code to make my queries do what I would like them to do.
I've been trying to come up with a solution to my problem with dates for some weeks now and nothing I've found seems to do the trick. I have found a great deal of information regarding date calculations in VBA or SQL but unfortunately I do not understand any of it. So, any help provided that uses the "basics" is most appreciated.
Here's the scoop:
The company I work for sends reports to other companies on a regular basis. These other companies must review the reports and respond back to my company within a certain number of days. My Supervisor would like to be able to calculate:
A) The number of days between two dates excluding weekends and holidays (If there are 8 days between DayA and DayJ, how many days are weekends and/or holidays, and how can we exclude these weekend and/or holiday days?)
B) An average of the dates calculated above (For any given report we have sent and have received in back, for any given time period, by each company).
THere is what I've created so far:
1) A table called "tblQuotations" which includes the fields "Date sent" and "Date rec'd"
2) A table called "tblHolidays 2010" which includes the field "HolDate"
3) A report called "rptTurnAroundTime" which includes the "Date sent" and "Date rec'd" fields as well as a "Count Days" field.
----> I have been able to count the number of days between two dates by creating a Report which uses the DateDiff function as follows: =DateDiff("d",[Date sent],[Date rec'd]). The calculation shows up in a field called "Count Days" which was made specifically to contain the calculation.
I hope I've covered everything. Please let me know if I can provide more information or explanation to help you solve my problem. Again, any help provided in most appreciated.
Thank you for your time,
Fresh
Go to the top of the page
 
Doug Steele
post Jul 5 2010, 01:55 PM
Post#2


UtterAccess VIP
Posts: 21,811
Joined: 8-January 07
From: St. Catharines, ON (Canada)


If tblHolidays2010 includes weekends, you could use something along the lines of
DateDiff("d",[Date sent],[Date rec'd]) - DCount("*", "tblHolidays2010", "HolidayDate BETWEEN [Date sent] AND [Date rec'd]")
Go to the top of the page
 
Gustav
post Jul 5 2010, 02:25 PM
Post#3


UtterAccess VIP
Posts: 2,163
Joined: 21-February 07



You can use this function:
CODE
Public Function ISO_WorkdayDiff( _
  ByVal datDateFrom As Date, _
  ByVal datDateTo As Date, _
  Optional ByVal booExcludeHolidays As Boolean) _
  As Long
#39; Purpose: Calculate number of working days between dates datDateFrom and datDateTo.
' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
' May be freely used and distributed.
' 1999-04-23. Gustav Brock, Cactus Data ApS, Copenhagen
' 2000-10-03. Constants added.
'             Option for 5 or 6 working days per week added.
' 2008-06-12. Option to exclude holidays from the count of workdays.
  Const cbytWorkdaysOfWeek  As Byte = 5
  ' Name of table with holidays.
  Const cstrTableHoliday    As String = "tblHoliday"
  ' Name of date field in holiday table.
  Const cstrFieldHoliday    As String = "HolidayDate"
  Dim bytSunday             As Byte
  Dim intWeekdayDateFrom    As Integer
  Dim intWeekdayDateTo      As Integer
  Dim lngDays               As Long
  Dim datDateTemp           As Date
  Dim strDateFrom           As String
  Dim strDateTo             As String
  Dim lngHolidays           As Long
  Dim strFilter             As String
  
  ' Reverse dates if these have been input reversed.
  If datDateFrom > datDateTo Then
    datDateTemp = datDateFrom
    datDateFrom = datDateTo
    datDateTo = datDateTemp
  End If
  
  ' Find ISO weekday for Sunday.
  bytSunday = WeekDay(vbSunday, vbMonday)
  
  ' Find weekdays for the dates.
  intWeekdayDateFrom = WeekDay(datDateFrom, vbMonday)
  intWeekdayDateTo = WeekDay(datDateTo, vbMonday)
  
  ' Compensate weekdays' value for non-working days (weekends).
  intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)
  intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)
  
  ' Calculate number of working days between the two weekdays, ignoring number of weeks.
  lngDays = intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))
  ' Add number of working days between the weeks of the two dates.
  lngDays = lngDays + (cbytWorkdaysOfWeek * DateDiff("w", datDateFrom, datDateTo, vbMonday, vbFirstFourDays))
  
  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

/gustav
Go to the top of the page
 
HiTechCoach
post Jul 5 2010, 03:08 PM
Post#4


UtterAccess VIP
Posts: 18,999
Joined: 29-September 03
From: Oklahoma City, Oklahoma


Fresh,
welcome2UA.gif
Welcome to UtterAccess!
What I do is calculate the respond/due date by add the number of work days, excluding week ends and holidays to a date.
THere at UtterAccess there is a great Access Code Archive form. I search on Workdays and found this great example:
fNetWorkdays and fAddWorkDays ...
Hope this helps ...
Go to the top of the page
 
Fresh
post Jul 6 2010, 08:35 AM
Post#5



Posts: 4
Joined: 30-June 10
From: Canada


Hello,
Thank you each for your responses, and the warm welcome HiTechCoach!
So far I can only understand Mr. Steele's response. I thought I had HiTechCoach's under control but then the code started getting longer and longer. Gustav your response is impressive but unfortunately much beyond my capabilities at this point. I really have no idea how to input anything like VB or SQL (which I am guessing is what you provided). I wouldn't even know where to begin with code that long. Unless I don't have to use those means to input this information? Is there another way? Well, regardless, I suppose it is better if I understand it first before just plugging it in. I will have to fix it if it goes wrong, and it will just be more of a mess if I don't know what I have to fix!
I'm going to give Mr. Steele's response a try and see what I come up with. I'll post with further questions (which I will likely have!).
Thanks again for your help.
Go to the top of the page
 
Doug Steele
post Jul 6 2010, 08:55 AM
Post#6


UtterAccess VIP
Posts: 21,811
Joined: 8-January 07
From: St. Catharines, ON (Canada)


See whether my article Access Answers: Working All Day (in Access Advisor) helps explain how to use VBA functions to do this any better. (You can actually download the sample database for free at Smart Access columns.)
Go to the top of the page
 
Fresh
post Jul 8 2010, 02:08 PM
Post#7



Posts: 4
Joined: 30-June 10
From: Canada


Hello,
lright. I've managed to learn a bit about VB and am proud to say I can work my way through Gustav's post. The trouble now lies in implementing this code. I have an Access 2007 book at my disposal called "The Missing Manual" which is helping me fumble my way through. Unfortunately it doesn't tell me exactly how to "get it done" as it spends most of it's time talking about how to Automate a Task (which unless I am completely off the mark, isn't exactly what I need it to do. Although I can now make a button called "The ButtonofPower"! acclaim.gif ). It does discuss how to Add a New Module (also completely new to me) which was helpful, but I just don't know where to go from there.
So, I think my best bet remains with my already created report and Mr. Steele's " =DateDiff("d",[Date sent],[Date rec'd]) - DCount("*", "tblHolidays2010", "HolidayDate BETWEEN [Date sent] AND [Date rec'd]") ". The link to "Access Answers: Working All Day" was quite helpful in explaining the "nuts and bolts" of the code, thank you.
Mr. Steele, you made mention that I could benefit from your equation if my tblHolidays2010 included weekends. This puzzles me and I was hoping you could expand on the suggestion. It is my understanding I could use the DateDiff function to count between weekdays and with some extra tweaking, exclude weekends (as Access would use it's own built in calendar to get this done). Is it only possible to do this if I have a table to draw from which lists each weekend of the year? What would the dates need to look like in this table? Should I list them as the dates (January 3rd, Saturday and January 4th, Sunday) or do I need to call them something specific?
Again, I thank Everyone for their input and help. It is most appreci
Go to the top of the page
 
Doug Steele
post Jul 8 2010, 02:24 PM
Post#8


UtterAccess VIP
Posts: 21,811
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Access has nothing built in to only calculate weekdays and omit weekends.
ead the article I cited for alternatives.
Go to the top of the page
 
Gustav
post Jul 9 2010, 06:29 AM
Post#9


UtterAccess VIP
Posts: 2,163
Joined: 21-February 07



Rename your table "tblHolidays 2010" to just tblHolidays.
pen a new Module.
Copy and paste my function into this.
Modify these lines:
' Name of table with holidays.
Const cstrTableHoliday As String = "tblHoliday"
' Name of date field in holiday table.
Const cstrFieldHoliday As String = "HolidayDate"
to match your table:
' Name of table with holidays.
Const cstrTableHoliday As String = "tblHolidays"
' Name of date field in holiday table.
Const cstrFieldHoliday As String = "HolDate"
Go to the menu and locate Compile and Save.
When prompted, provide a name for the module like basTime.
Now you can call the function as any other function of Access (VBA).
Thus, in your report your controlsource should read:
=ISO_WorkdayDiff([Date sent],[Date rec'd],True)
Have fun!
/gustav
Go to the top of the page
 
Fresh
post Jul 9 2010, 09:54 AM
Post#10



Posts: 4
Joined: 30-June 10
From: Canada


Hello,
HOO HOO!! It worked, it worked, it worked!!
Once I figured out how to connect the VB to the Report it was easy sailing! Thanks for all your help, especially Gustav who "showed me the way".
Now, I wonder if my Boss will give me the afternoon off for all "my" hard work....??
Thanks again,
Enjoy your weekend!
Go to the top of the page
 
Gustav
post Jul 9 2010, 04:04 PM
Post#11


UtterAccess VIP
Posts: 2,163
Joined: 21-February 07



Have a nice weekend!
gustav
Go to the top of the page
 
Cindy_2010
post Aug 31 2010, 04:33 PM
Post#12



Posts: 1
Joined: 31-August 10



[quote name='Gustav' post='1996066' date='Jul 9 2010, 04:29 AM']Rename your table "tblHolidays 2010" to just tblHolidays.
pen a new Module.
Copy and paste my function into this.
Modify these lines:
' Name of table with holidays.
Const cstrTableHoliday As String = "tblHoliday"
' Name of date field in holiday table.
Const cstrFieldHoliday As String = "HolidayDate"
to match your table:
' Name of table with holidays.
Const cstrTableHoliday As String = "tblHolidays"
' Name of date field in holiday table.
Const cstrFieldHoliday As String = "HolDate"
Go to the menu and locate Compile and Save.
When prompted, provide a name for the module like basTime.
Now you can call the function as any other function of Access (VBA).
Thus, in your report your controlsource should read:
=ISO_WorkdayDiff([Date sent],[Date rec'd],True)
Have fun!
OGustav
can you please show my how to call this module from query.
Thank you
Cindy
Go to the top of the page
 
HiTechCoach
post Aug 31 2010, 05:00 PM
Post#13


UtterAccess VIP
Posts: 18,999
Joined: 29-September 03
From: Oklahoma City, Oklahoma


Cindy,
Follow the steps to place the code in a module.
In a query you can call the function ISO_WorkdayDiff()
You could use it in a a calculated field.
The SQL would look something like this
CODE
... , ISO_WorkdayDiff([DateField1],[DateField2],True)  as NumWorkDays, ...
Go to the top of the page
 
GaryCFWH
post Oct 11 2010, 07:27 AM
Post#14



Posts: 11
Joined: 8-September 10



Thanks.
Go to the top of the page
 
Barbll
post Jan 13 2011, 01:35 PM
Post#15



Posts: 36
Joined: 13-January 11
From: Ontario, Canada


I got this to work in an Access query but I stumbled at first on how to 'call the function' and then had to remember how to do a calculated query. It works great. I put the following on the Field line of the query which created a new field/column in my query:
ewFieldName: ISO_WorkdayDiff([DateSent],[DateReceived],True)
The VBA code part was easy - simply copying and pasting the code into a module screen and saving it.
Thank you!
Barbara
Go to the top of the page
 
Barbll
post Jan 13 2011, 02:38 PM
Post#16



Posts: 36
Joined: 13-January 11
From: Ontario, Canada


Thank you so much for this code. I don't know VBA but it works! I notice that if the DateReceived and DateSent are the same date the count = 0. Also when subtracting the two dates it doesn't include the date received, ie, I need it to count 3 if it was Received Monday and Sent Wed.
believe I may have resolved this by inserting +1 after the function? Like this: =ISO_WorkdayDiff([Date sent],[Date rec'd],True)+1
Can you tell me if that would be an acceptable fix or should I be looking for another VBA code?
Go to the top of the page
 
HiTechCoach
post Jan 13 2011, 03:14 PM
Post#17


UtterAccess VIP
Posts: 18,999
Joined: 29-September 03
From: Oklahoma City, Oklahoma


That is the correct way to caclulate the difference between to dates. 0 means that is no difference.
You could to do that.
Go to the top of the page
 
Gustav
post Jan 14 2011, 02:06 AM
Post#18


UtterAccess VIP
Posts: 2,163
Joined: 21-February 07



Thanks for the feedback.
Yes, adding 1 to your count of workdays, as you explain it, is the correct method.
The other method would be, of course, to modify the function, but I think it basically does it right so I would be reluctant to do so.
/gustav
Go to the top of the page
 
teoteodoridis
post Feb 3 2011, 08:48 AM
Post#19



Posts: 11
Joined: 3-February 11



hello!there is a function that will probably work on that issue.it has like that
supposed we have a startday and an endday so as to count only the working days
Oalready use it at a programe i have created for emploees who take teir annual holidays (it doesn't include national holidays)
Function Work_Days(StartDate As Date, EndDate As Date) As Integer
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer


On Error GoTo Err_Work_Days


WholeWeeks = DateDiff("yyyy", StartDate, EndDate)

DateCnt = DateAdd("y", WholeWeeks / 365, StartDate)
EndDays = 0
Do While DateCnt <= EndDate
If Format(DateCnt, "ddd") <> "Sut" And _
Format(DateCnt, "ddd") <> "Sun" And IsNull(DLookup("[holidays]", "tblholidays", "[holidays]= # " & DateCnt & "# ")) _
Then

EndDays = EndDays + 1


End If

DateCnt = DateAdd("d", 1, DateCnt)


Loop
Work_Days = WholeWeeks / 365 + EndDays



Exit Function
Err_Work_Days:
' If either BegDate or EndDate is Null, return a zero
' to indicate that no workdays passed between the two dates.
If Err.Number = 94 Then
Work_Days = 0
Exit Function
Else
' If some other error occurs, provide a message.
MsgBox "Error " & Err.Number & ": " & Err.Description
End If
*as it is obvious you have to create a table (updated via a form)
which includes the spesific days of the national holidays(or whichever date is considered as day off)
i hope it works!
**i call the function on the update event of the field (in a form) that caries the enddate value(date)
Go to the top of the page
 
Gustav
post Feb 4 2011, 04:36 AM
Post#20


UtterAccess VIP
Posts: 2,163
Joined: 21-February 07



Teo, this won't work reliably as 365 doesn't take leap years into account.
urther, the use of localized strings as "sat" and "sun" prohibits the code to work in non-English environments.
/gustav
Go to the top of the page
 
3 Pages V  1 2 3 >


Custom Search
RSSSearch   Top   Lo-Fi    19th July 2018 - 11:35 PM