Hi All,
I have been trying to generate a formula or a macro in order to automate the daily production.
I have stored the daily production ours in in B452 (1 cell only). It may change from 7 hours to 14 hours any any point. Hence controlling through 1 cell is convenient.
My job hours are allocated in J Column.
Required Completion dates are in E column.
My job numbers are allocated in column T.
I have formulated a calendar horizontally.
Row number 51 has all the days.
Row number 52 has all the dates.
Row 53 has daily capacity in hours (referred by cell B-452 as mentioned above).
I have managed to Automate this calendar so that Monday-Friday show the capacity as entered in cell B-453 (currently 14 hours a day) and Saturday-Sunday show zero (referred by cell number B-451 for Saturday and B-450 for Sunday).
Now, Row number 54 is where I need all the production hours to automatically get split depending upon what is the capacity of that particular day. In other words the minimum amount of hours should be controlled by capacities shown on row 53 and any remainder hours should be moved on to the next cell. In doing so, is it possible plot the progression dates of any particular job in columns U-Z. For example job number "602A427-1" takes 20 hours to complete. Required Completion date is 03/05/2021. Job has been automatically planned on 22/4/2021 (14 hours - Column U - Row 66), 23/4/2021 (remainder 6 hours - Column V - Row 66). This will help me plot a daily schedule for the machine operators as job number "602A427-1" will be started on 22/4/21 and will be completed by 23/4/21. To give you an idea I have managed to automate the plot as well wherein currently it refers column U-Z. The only thing is currently I am manually typing all the progression dates.
I may not be clear in my explanation. I would appreciate if you could attempt to address this query. I will be able to explain better as we progress through this example.
Thank you
Bookmarks