Maverick2378
asked on
Calculate Start Date/Time based on End Date/Time and working hours excluding breaks, weekends and holidays
I need a function for MS Access vba that will calculate a start date/time given the end date/time and the number of hours it takes to complete a job. I have a table called tblBreakSchedule that has 3 fields: ID, TimeOfDay, and WorkingMinute. The table has 1440 records (one for each minute of the day). It lists the time as HH:MM AM or PM in the TimeOfDay field and if it is not during a break a one is listed in the WorkingMinute field signifying one working minute. Otherwise a zero is listed. I also have a table called tblHoliday with one field called Holiday that lists dates as mm/dd/yyyy.
I have been banging my head against my desk for several days now trying to figure this one out, but to no avail. There seems to be too many variables to account for. I have scoured the internet for a solution, but haven't found a whole lot....just the hours between two dates, but not accounting for the other variables.
Any help would be greatly appreciated. Thanks in advance.
I have been banging my head against my desk for several days now trying to figure this one out, but to no avail. There seems to be too many variables to account for. I have scoured the internet for a solution, but haven't found a whole lot....just the hours between two dates, but not accounting for the other variables.
Any help would be greatly appreciated. Thanks in advance.
Having 1440 records to account for each minute of the day is a red flag that shows you are going about this database design the wrong way. Can you explain how your process works and exactly what it is you want to accomplish?
Upload a sample database.
And include few records and list the expected output. Also, as IrogSinta suggested how do you process each record to get the required output.
And include few records and list the expected output. Also, as IrogSinta suggested how do you process each record to get the required output.
ASKER
I completely agree that the tblBreakSchedule table is not the most efficient design, but I just couldn't think of any other way to get around it.
Here's what I'm trying to accomplish.....
I have a read only ODBC link to an ERP system. I have created a series of queries to generate a multilevel BOM for a particular part number. The table listes (from left to right) Level 7 Items, Level 6 Items, Level 5 Items,...., Level 2 Items, Level 1 Items, Level 7 Build Time, Level 6 Build Time,...., Level 2 Build Time, Level 1 Build Time. The table is sorted in descending order by Level 2 Build Time. Each Level 2 Item may consume 0 to X Level 3 Items, each Level 3 Item may consume 0 to X Level 4 Items and so on.
I wrote a vba macro to give me an optimum order in which to release each part to the shop floor. It starts with the Level 7 Items because you must have all of the necessary Level 7 Items in order to produce the Level 6 Items and so on. The code finds the first Level 7 Item (if there is one) within the first Level 2 Item and populates a text box with its value then moves to the next record checks the following:
1. Is the record still within the first set of Level 2 Items? If not, start with the Level 6 Items.
2. Is the field null? Is so, go to the next record.
When it reaches a Level 2 Item, it puts it in the text box and moves on to the next record in the form, while the recordset loops back to start with the Level 7 Items for the next Level 2 Item.
When the macro finishes, I have a list of items in the order that I want to release them to the shop floor, but I need start dates and times for each of them. Alone this would be fairly simple, but we have a first shift and a third shift, there are breaks within each of those shifts that may change every once in awhile. We also have a dynamic workforce whom are all crosstrained to work on several different aspects or our equipment and can be moved from one task to another rather easily. More or less, the schedule is very erratic and our manpower is not a constant, but I want to get it as close as I possibly can and then be able to tweek things when needed.
That being said, our shop is divided into Departments and Work Centers and each part will go through at least one department and at least one work center during its production process. The departments are as follows: Prep, Machine Shop, Fab, Assembly, and Paint. Each of these departments has several work centers. The Prep and Machine Shop Departments do not have to factor in manpower because only one part can be on a machine at one time. However, with the Fab, Assembly, and Paint Departments, we can add more people to reduce the production time. For instance, if Fab is working on a weldment that takes 500 hours to build, and we have 5 people working on it, then we should be able to build it in 100 hours plus breaks, weekends, etc. Only one part can be in a work center at a time, so the macro must take that into account along with weekends, holidays, break time, and manpower.
I've attached a sample database, but I'm not sure how useful it will be considering the ODBC link. The button which should contain the macro that I'm having issues with is frmPartOrderWithRoutings. The button is at the top left of the form (Get Times).
NOTE: The last record should should display the Project End Date and Time in the End text box because we are "Backward Planning". If we were "Forward Planning" then the first record would display the Project Start Date in the Start text box. At this time I'm not worried about Forward Planning.
I hope I didn't ramble on too much. I know it's alot to take in all at once. Thank you so much for your help.
Scheduling-Database--Test2-.accdb
Here's what I'm trying to accomplish.....
I have a read only ODBC link to an ERP system. I have created a series of queries to generate a multilevel BOM for a particular part number. The table listes (from left to right) Level 7 Items, Level 6 Items, Level 5 Items,...., Level 2 Items, Level 1 Items, Level 7 Build Time, Level 6 Build Time,...., Level 2 Build Time, Level 1 Build Time. The table is sorted in descending order by Level 2 Build Time. Each Level 2 Item may consume 0 to X Level 3 Items, each Level 3 Item may consume 0 to X Level 4 Items and so on.
I wrote a vba macro to give me an optimum order in which to release each part to the shop floor. It starts with the Level 7 Items because you must have all of the necessary Level 7 Items in order to produce the Level 6 Items and so on. The code finds the first Level 7 Item (if there is one) within the first Level 2 Item and populates a text box with its value then moves to the next record checks the following:
1. Is the record still within the first set of Level 2 Items? If not, start with the Level 6 Items.
2. Is the field null? Is so, go to the next record.
When it reaches a Level 2 Item, it puts it in the text box and moves on to the next record in the form, while the recordset loops back to start with the Level 7 Items for the next Level 2 Item.
When the macro finishes, I have a list of items in the order that I want to release them to the shop floor, but I need start dates and times for each of them. Alone this would be fairly simple, but we have a first shift and a third shift, there are breaks within each of those shifts that may change every once in awhile. We also have a dynamic workforce whom are all crosstrained to work on several different aspects or our equipment and can be moved from one task to another rather easily. More or less, the schedule is very erratic and our manpower is not a constant, but I want to get it as close as I possibly can and then be able to tweek things when needed.
That being said, our shop is divided into Departments and Work Centers and each part will go through at least one department and at least one work center during its production process. The departments are as follows: Prep, Machine Shop, Fab, Assembly, and Paint. Each of these departments has several work centers. The Prep and Machine Shop Departments do not have to factor in manpower because only one part can be on a machine at one time. However, with the Fab, Assembly, and Paint Departments, we can add more people to reduce the production time. For instance, if Fab is working on a weldment that takes 500 hours to build, and we have 5 people working on it, then we should be able to build it in 100 hours plus breaks, weekends, etc. Only one part can be in a work center at a time, so the macro must take that into account along with weekends, holidays, break time, and manpower.
I've attached a sample database, but I'm not sure how useful it will be considering the ODBC link. The button which should contain the macro that I'm having issues with is frmPartOrderWithRoutings. The button is at the top left of the form (Get Times).
NOTE: The last record should should display the Project End Date and Time in the End text box because we are "Backward Planning". If we were "Forward Planning" then the first record would display the Project Start Date in the Start text box. At this time I'm not worried about Forward Planning.
I hope I didn't ramble on too much. I know it's alot to take in all at once. Thank you so much for your help.
Scheduling-Database--Test2-.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.