Help

Calculating hours planned between two dates

Topic Labels: Formulas
962 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Chris_H1
5 - Automation Enthusiast
5 - Automation Enthusiast

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

1 Reply 1

Hello @Chris_H1!

I want make sure I understand your table set up before recommending any formula changes. So in your schedule production table you have reference to the average hours planned of work per week for each task for a production.

Then you have a separate table tracking the Weeks in a year. So if I understand correctly you are now trying to find the sum together the hours that all the tasks will take for a singular week of production? If that doesn’t sound right please feel free to let me know. Screenshots would also be amazing if you’d feel comfortable sharing them!

Also, did your formula work or was it not returning what you were expecting it to?