Hello there. I’m struggling with a formula on base I use for production planning - wondered if anyone has time to give me some pointers…
I have a table we use to schedule production - in essence the table has rows of tasks, with amongst other things, a start date, end date, total number of hours planned for the task, calculations for number of days between start and end, and then calculations for the average hours per day and per week. All working great and visualising on a Gantt.
I now need to see an indication of load on production based on a sum of the average weekly hours for any given week. I’ve created a separate table with rows for each week of the year, with a week commencing date as the primary field, and linked through to my schedule. I’m trying to write a formula that returns a sum of the average weekly hours for tasks in my schedule where the week commencing date in the production load table falls between the start date and end date in the schedule table.
I initially had a go at producing an array of dates between start date and end date in my scheduling table to use as the basis for a weekly sum, fell flat on that one and not sure it was the right approach. Best attempt at a calculation in my production load table was:
IF(AND({Week Commencing}>{Start date (from Link to Jobs)}), {Week Commencing}<{End date (from Link to Jobs)},(SUM({Average Weekly Hours (from Link to Jobs)})))
Feel free to chuckle but any constructive suggestions of how I could actually do this would be greatly appreciated!
Chris
