Modules & VBA :: Calculate Deadline With Workdays And Holidays
Mar 19, 2014
I am trying to get a module set-up that will add a number of workdays (no weekends, no holidays) to a date that is mentioned in a form and put the resulting deadline date in another field on the same form (date or remaining days to deadline would be even better).
Now it gets a little tricky, the module will need to select different amounts of workdays to be added to the date in the form depending on what status is selected (different statusses have different amounts of days).
I'll probably need:
List of holidays
List of days required per status
Start date (to which the days can be added).
Example:
Status = "DQ" which has 2 days to work with.
Start date in form = "21/03/2014".
Result = 25/03/2014 or preferably "2 days remaining".
Currently I use a module to calculate DueDate, excluding weekends and holidays. I store all the holiday in a tblHoliday and reference this table in the module. A formula then calculated the DueDate. This works great in an access form, but I now realize that I need that value stored so it appears on the table as well, but do not know how to accomplish this.
I am using the function below to calculate a field that excludes weekends and holidays. The weekends are excluded as it is now, but when I try to add in code to exclude holidays I am getting errors. The code for the holidays is in bold and a couple of the errors are Loop without Do so I remove the Loop then I get a Else without If.
Code: Option Compare Database
Public Function WorkingDays(Due_Date As Date, Result_Date As Date) As Integer '-- Return the number of WorkingDays between Due_Date and Result_Date On Error GoTo err_workingDays
In the attached image I was trying to calculate number of working days(throughput) b/w item received_date and item_released date by using datadiff with "w" interval . but if I use "w" interval it brings value zero .but if I use "d" interval then it brings result with inclusive of Saturdays and Sundays.i don't need that.
How to calculate the number of working days exclusive of Saturdays and sundays.
I have been looking for quite some time for some vba code that would return a date based on values in 2 fields.
In other words, I already have a field that returns transit days based on the customer chosen. End users will then enter a DueDate for the order. I want a field that returns a "ScheduledShipDate" based on [DueDate]-[TransitDays]. The part that makes this more difficult, in my opinion, is the fact that I also need to take into account some specific holidays.
I have already constructed a table with a list of the 6 company holidays and their corresponding dates. [tblHolidays.HolidayDate]
Unfortunately, my vba knowhow might as well be limited to copy and pasting as I do not need to use it all that often.
I've been trying very hard to get through the various instructions, code, modules, etc, on calculating workdays. I am having no luck in getting this done. As one who can write an Access database only to the point of a little VBA, using mostly queries and formulae to get by, I have difficulty with posted code from time to time.
For example, in a previous thread (actually, in several previous threads), code was posted with the instruction (essentially), "Here. Use this." To which someone replies, "Thanks, worked a charm." Or the like. As one who struggles and has limited responsibilities in this area (and little time to learn while on the job), posted code can be a blessing... but "Here. Use this." can be its attached curse. "HOW do I use it?" is the next question. Is it a module? Do I put it as code on the On Open event for a form? Is it an expression I build into a query? Sometimes, seeing the code is only the first step... and for one like me, can lead to a lot of trouble.
Also, many have been kind enough to post databases, which show the end result of much work. This is also greatly appreciated, believe me. But, again, once I start looking to HOW it was built... the investigation sometimes leads nowhere. Where do I look for code? Did I miss something? Why doesn't mine work like that?
So, for the question I have now, I have searched this and other forums. I have copied and pasted code that I thought should work. I have copied code into a module and tried to call up that module in a query. I have followed instructions as well as I could... but you can guess where this is headed. What I have is, in the simplest form, a table called Employees with the fields Name, StartDate and EndDate. I need to know how many workdays there are in the date range of StartDate to EndDate, including the dates in those fields.
If you have answered this question before, I apologize for not being able to figure this out. Any help is greatly appreciated.
I have 2 dates that I need to count between. Easy enough just use the datediff right? Nope cause it won't count just the 5 workdays. I researched and found that the "w" in the function doesn't work the way I need it to. I found lots of code to make a module that will do it for me and they all include having a holiday table. Right now I don't need a holiday table I just want the simple dates in between.
Please, please help as I’ve got a really urgent project to complete. If I don’t get it finished I’ll be out in the cold. I’m really struggling for time myself so I hope people here can help. Here’s the rub:
I got a list of recipients (names & addresses) each requiring a list of items (we have all the lists currently on bits of paper). The number of addresses and hence the number of lists runs into the millions so I need a big database. Will Access be able to do this?
We have all the stock in our warehouse in stock ready to pick. We use to hand make the stuff but now we just buy it in. So at some point I’m going to need a buying schedule (future development).
Contrary to popular belief we the deliveries to the recipients are over several deliveries (not one). What I need is a database that allows me to produce a schedule of which go on which delivery (essentially I do this by postcode/zip code). I need the schedule so the pickers know how to assemble each load. Also there’s a claus in the contract that say we have to have a delivery schedule for the guy doing the deliveries (the same guy – my boss – does all the deliveries). His missus is funny, she keeps saying to him “Look out for the rain dear”. Thankfully we never have to deal with returns so no issues there.
I there also a way to monitor which picker picks what because in the past some of them have been a bit crap and they should give themselves a kick up the backside.
I also need to produce a run of labels so that I can stick a label on each item saying give the name of who the item is for. Is this possible? It’s now joke having to write them all by hand.
One more thing. Some of the delivery points have been known to leave gifts when the delivery is made. We use to just accept the gifts and say nothing about it but now we need to record this as the tax man says because of the sheer number of gifts then this becomes benefit in kind and has to be declared. Thankfully the company vehicle is except from tax (at least for now).
I can’t offer any money for this. We’re a kind of charity. My deadline is 4½ days and counting. I really hope yule all help with this because it’s really important and if it’s not done I’ll lose my job.
I haven’t got very far with this and I really haven’t got time to do it myself. If someone could send a sample database of how I should start, that would be great.
I created a form using a text boxes by using the field list icon and dragging the field I wanted to make into the text box onto the form. In the properties of that text box, under the format tab I set the format for "short date". Under the data tab in the properties of the text box I put in the control source this forumla =DateSerial(Year([Date of Service]),Month([Date of Service])+10,Day([Date of Service])). "Date of Service" on my form is the name of the source field it calculates from. What the forumla does is calculates from the date entered into my Service Date field and whatever date is in that field it adds 10 months to it. This field with this formula in it caluclates the date of my contacting the customer to setup an annual service for them. I also have another field that calculates the annual service date using the same formula only changing the +10 to +12.
When the Date of Service it entered the Contact Date field automatically adds 10 months to the date entered into the Date of Service field. Same of the Date of Next Service field only instead of 10 months it adds 12. So my formulas are correct. However when I go to my table I can not see any of the caluculated dates entered into the table. I can see it in the datasheet view of my form. My question is how do I create a query to pull only contacts dates from this form for a specific time frame? When I attempt to create a query I can only see table fields which do not contain the formulas for calculating the dates. Or can the fields that calculate the dates on my form be linked so the calculated dates are linked into the table and updated on the table when I update the form?
Keep in mind I am new to access so please give very detailed and specific instructions on a solution for this issue.
This is probably an easy criteria but I'm getting close to COB and I have to have it in today.
I have a DOB as (DD Mon YY 04 Jul 79) in which I need to create a column for thier age. Then I have to show all the people who are over 60 years old. I can't even get thier age to show correct. Any help. Thanks
I need to enter a number in the Life Skills Credits. The Earned field should continue to add what I enter in the Life Skills Credits, but I need the life Skills Credits to revert back to 0 (zero)
I have a form with a subform. The form is the date field. The subform are all the stores delivered to on that date. I need to have a command button to open an email with the current date showing and list all the stores with their delivery numbers next to it inside the email.
Not really sure how to word this as I'm not sure if I even understand. Basically I have been asked to set up a database logging distubances and need to display on the form 3 different dates (action by, monitoring period and date to close case) from the date the data is entered, depending on the disturbance chosen.
So if there was noise nuisance the action date would be 7 days, the monitoring period would be 14 days and the closing date would be 90 days. This would be different from, say dog fouling. When the user chooses the disturbance from the combo box I need the dates to reflect the deadlines for that disturbance.
I have entered the number of days to add onto the table containing the disturbances (ie 7, 14 etc) and know how to add these days onto the original date.
How do I make this happen depending on the disturbance selecting.:confused:
I have a form that pulls data from a table. Basically the form is just a postcard. I have our company name, address, PO Box, city, state and zip in the upper left corner. My fields in the center of the postcard tie back to a table that contains the contact companies name, street address, PO Box, city, state and zip. My question is this: How do you format the contact company, address, PO Box, city, state and zip so that it appears uniform when printed? Some of the contact companies will have a street address but not a PO Box. How can I have the form print so the PO Box does not leave that field blank and pulls up the city, state and zip into that empty space? Also if one company's city is shorter than another how can it be formatted to pull over the state to make it uniform?
Any help on this would be greatly appreciated. I am under heavy deadline to have this form corrected and working by the end of the week.
Thanks in advance, cnut1
Attempted to add the database but it would not upload for some reason. Will try again tomorrow. I am sure the way I am doing some things are not necessarily the best or easiest way since I am still learning Access. The form I am looking for help on is frmpostcardJanfrt.
Thanks to all who have provided help on other threads. I have made some progress but I am still struggling with the last couple of relationships here. I am tracking projects and have normalized this data into the following tables:
tbl_customers (contains customer specific information)
Is on the "one" side of a 1:many relationship to tbl_workorders. (1 customer for multiple WO's) Is on the "one" side of a 1:many relationship to tbl_locations (up to 2 locations per customer). Is on the "one" side of a 1:many relationship to tbl_circuits (up to x number of circuit ID's for each customer)
tbl_workorders (contains order specific information)
Is on the "one" side of a 1:many relationship to tbl_costs (multiple costs for each WO). Is on the "one" side of a 1:1 relationship to tbl_services (each WO can only have one row on the services table. I suppose I could combine all fields onto 1 table, but logically they represent different information. The WO table is about the work order and the other table deals with the customer's services.) Is on the "one" side of a 1:many relationship to tbl_contacts (can be multiple internal contacts for each WO)
tbl_dates (contains as many as 33 possible dates for each project)
This table is one that I am having trouble with. Each work order can have any of the possible dates on tbl_dates. I have designed tbl_dates to be as normalized as possible. You select a date type and enter the date. I can't figure out where to relate this to tbl_workorders without using the WO field. Perhaps I am designing this table incorrectly.
OK Here's the deal: An employee has 0 holiday days in the first year of employment and accumulates 1 holiday day for every 1.2 months worked after the first year of employment. So after 2 years of working the employee is entitled to 10 holiday days, but if the employee only takes 5 holiday days they should be able to accumulate the rest of thier days into their next year.
I've added the database that I've created so far, I haven't got too far, and I realize that I will have to create a field in the employee table with the first day of employment.
I need a query to tell me how many staff are on holiday on the same day..here what i have so far.
A query with name, startholsdate,endholsdate, totaldays, etc, etc,,
records showing; employee A books 01/01/06 to 14/01/06 and employee B books 10/01/06 to 24/01/06 C books 01/02/06 to 10/02/06 and so on,
I need to show how many employees are off on what days eg employee A and B are on hols between 10/01/06 and 14/01/06, so only 3 more staff may take days off between the 10th and 14th. I have tried all sorts of calculations but to no avail? can anyone help..
I have to make a form with multiple sections. Each section has n questions to which corresponds a numerical value from 1 to 4.
Example:
Question 1 value 2 Question 2 value 1 Question 3 value 3 Question 4 value 2 Question 5 value 4 Question 6 value 2
I have to choose the most recourrent number like the Excel MODA function then in this example is 2.But if there are more then one recurrence with the same times i must choose the highest one.
Question 1 value 2 Question 2 value 1 Question 3 value 3 Question 4 value 2 Question 5 value 4 Question 6 value 4
In the example above number 2 and 4 recur the same number of times and i must choose the number 4 because is the higher.
There is not a MODA function in Access, even if Excel, with the second example returns the most recurrent but the lower value 2.
I am stuck where to start and wanted some advise. I want to create a database that captures whether employees are late, sick, holidays etc.
So i would have a list of employees, then create a new records in another table to store what type of reason it is, (late, sick, holidays etc.), and then the directors can monitor employee sickness. But what i would to do is to be a bit clever about it though in 2 ways.
Against the employee they would have a number of allocated holidays against them, but i just wanted the user to enter the start and end date, and then get access to work out how many holidays days need to be taken off, i.e. it does not included weekends or bank holidays....
Also in the sickness report, work out how many single days where taken on either a Monday or Friday....
Has anyone done or seen an access database that can do this already.
I know there is a calendar addin, but i've not used it...
i'm trying to build something that knows when to notify someone that they can call a trade, given 1) a callable date 2) a notice period and 3) the relevant cities
'--------------------------------------------------------------------------------------- ' Procedure : NoficationDate ' Date : 28/03/08 ' Purpose : to calculate the date of notification for an EMTN, given the call date & notice period & cities '--------------------------------------------------------------------------------------- ' Public Function NotificationDate(dtCall As Date, intPeriod As Integer, strSixDigitCities As String) As Date
Dim intWorkingDaysBefore As Integer Dim strCities(2) As String Dim dtLoop As Date
Do dtLoop = dtLoop - 1 If Left(Format(dtLoop, "ddd"), 1) <> "s" And IsBankHoliday(dtLoop, strCities(0)) = False _ And IsBankHoliday(dtLoop, strCities(1)) = False And IsBankHoliday(dtLoop, strCities(0)) = False Then intWorkingDaysBefore = intWorkingDaysBefore + 1 End If
Loop Until intWorkingDaysBefore = intPeriod
NotificationDate = dtLoop
End Function
'--------------------------------------------------------------------------------------- ' Procedure : IsBankHoliday ' Date : 28/03/08 ' Purpose : to see if it's a bank holiday '--------------------------------------------------------------------------------------- ' Public Function IsBankHoliday(dtInput As Date, strCity As String) As Boolean Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qry_Tass_All_Hols WHERE CITY = '" & strCity & "' AND HDATE=#" & Format(dtInput, "mm/dd/yyyy") & "#", dbReadOnly) If rs.RecordCount > 0 Then IsBankHoliday = True Else IsBankHoliday = False End If rs.Close Set rs = Nothing
End Function
BUT it runs like arthritic toad, it makes a minute per execution and i was hoping to scale it up to 4000 records => 2 days of run time :eek:
any ideas on how to attack this problem...even guesses appreciated, i can try things out and see if they work
I have a Floating Holiday that doesn't happen every year how do I incorporate this in Access 2010, I have a report that calculates Paid Time Off for employees and when they have a year that has a Floating Holiday or doesn't have a floating holiday, how do I incorporate that into a query or report without losing the calculations from the previous year reports.
for example, this year a floating holiday is allowed so my calculations work, but next year if a floating holiday isn't allowed how do I make it a 0 without losing this years data.
I tried tblFLOATINGHOLIDAY with several years listed (at first used Yes/No) and a field that you either put 8 or 0 (8 hours or 0 hours). Then I used a query combining tblEMPLOYEES and tblFLOATINGHOLIDAY with citeria for FLOATINGHOLIDAY to be 2015 so all the records would show 8 under FLOATING HOLIDAY. But it comes up with no records.
