I'm moving a production facilities schedule from excel to Access. I'm having trouble calculating out dates. I basically have a schedule that has a ship date. From that, based on the product type, we have a productime time line for all the steps to process these builds, which gets me to the start on line date.
To try to be as less confusing as possible, below is a snap shot of the table. Product line is the identifier to processes and time (days) to accomplish a build.
My calculation needs to be my ship date minus the number of days below for each step to get me to the SOL Date, which is when we need to start building.
However, I have to add a variable... days we are not working on the line.
I have a table named calendar, which is basically every day of the year (10 years out) with a yes/no box. So, I need to be able to take the [Ship date] - Each step below to get my date. My problem, is I also need to look at my calendar table and only count the work days.
Something like [Schedule].[Ship date] - Count(Where value is true[Calendar].[CheckBox])=Each column belows Date
I'm sorry, I tried to make that simple, and it sounds confusing to me, which is what I've done in my head... I think I have made this more complicated by over thinking it all... any help is greatly appreciated.
For Product Line 1, I need to count only the workdays going backword for each column, and subtract that back from my ship Date.
Build Time, is total workdays needed to build.
Product Line (Category) Build Time SOL Sub-Weld Oven Main Weld Ship to Paint/Machining Back From Paint Assembly/Test/PDI/Pack 1 15 1 2 2 0 7 1 2 1M 20 1 2 0 1 13 1 2 2 14 4 4 0 4 0 0 2 3 15 4 4 1 4 0 0 2 4 6 1 0 1 3 0 0 1