List days capacity between two dates

Question

I'm trying to calculate the workload of my department while adding new projects. So I have a table for Projects submitted and a table of employees' availability dates.

I need a formula that does the following:
It lists the days between submission and deadline dates of new projects and retrieves value in a field [capacity] corresponding to those days from a child table and sums them.

For instance, if I add a record with the following data:

Submission date: Feb 23rd
Deadline: Feb 26th
it returns:
sum(Feb 23 [capacity] + Feb 24[capacity] + Feb 25[capacity] + Feb 26 [capacity])

If you calculate it manually according to data in screenshot it should return 36.

Thank you

donlarson · Answer

Karim,To retrieve the values of [Capacity]&nbsp; you will need to establish a relationship between your Project Record and the records for the inclusive dates of the project.You did not show the Record ID#s but lets assume you data looks like this and your new project is RID =2RID, Date,&nbsp; Day Capacity, Related Project54, 23 Feb, 9, 2&nbsp;55, 24 Feb, 9, 256, 25 Feb, 9, 257, 26 Feb, 9, 2You now can easily write a Summary field between Projects and Capacity that will total [Day Capacity]The obvious problem is that you have to relate the days in Capacity to Projects for this to work.&nbsp; &nbsp;@Mark Shnier (YQC) is an expert on changing Key Fields so that you can use Dates to pull together data.&nbsp; &nbsp;I suspect this one will challenge even him because you have a dynamic number of dates that have to be related to the Capacity table.&nbsp; &nbsp;He needs to weigh in here for a potential native solution.However, if I am correct and this is not going to work natively,&nbsp; you are going to need to write some scripts&nbsp; to do this one for you.​&nbsp; &nbsp;The math for this sort of look up is not complicated.&nbsp; &nbsp;I a few paragraphs of PHP by someone that knows the QB API's&nbsp; will give you the answer.&nbsp; &nbsp;There is a real advantage to a scripted solution which is that you will get a static value that you can store when the Project is created or evaluated.&nbsp; A pure Quick Base solution where things are dynamically calculated will change as the Days Capacity change.------------------------------Don LarsonPaasporterWestlake OH------------------------------

markshnier__you · Answer

Karim, as a followup to Don's post, what are you hoping to happen with the available capacity after the project is entered? Are you expecting it to decrease?

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

karimgawish · Answer

I think I should have another column with the decreased capacity.I think I didn't describe the problem properly, I'll try to describe it in a better way.Let's say we manufacture chairs, we receive projects daily, each project has a set number of chairs.Each employee can make 3 chairs per day, everyday they're at the office.When I receive a project request to make 25 chairs from Feb 23 to Feb 26, I want my total capacity for those days calculated and the number of chairs required deducted sequentially from the daily capacity and stored in the sheet.In the screenshot I wrote the numbers I expect the formula to provide.

23rd we'll do 9 chairs, so ending capacity is 0
24th we'll do 9 chairs, ending capacity is 0
25th we'll do the remaining 7 chairs,&nbsp; ending capacity is 2
26th will remain unchanged since we finished the project the day earlier.

That way when we receive a new project, using the ending balance, we can automatically know if our remaining capacity can fit the new project or not.The following assumptions stand:- Projects are First come first serve basis - hence sequential deductions- "Chairs" are identical to each other, and all employees have the same capacity to make 3 chairs every single day at the office. Projects differ in the amount of chairs only.I appreciate your help since I've been stuck here for some time.------------------------------Karim Gawish------------------------------

karimgawish · Answer

-------------There is a real advantage to a scripted solution which is that you will get a static value that you can store when the Project is created or evaluated.&nbsp; A pure Quick Base solution where things are dynamically calculated will change as the Days Capacity change.--------------Having it dynamically calculated and updated is not gonna be a problem at all, I can find a workaround to store the "state" of the project at the time of submission.(Checkbox to be ticked while adding a new project confirming our knowledge that we might not meet due date for example)So a pure Quick base solution can still be an option.Thank you for your help.------------------------------Karim Gawish------------------------------

markshnier__you · Answer

My native solution is to create a middle table called Project Day Assignments

1 Project has many Project Day Assignments
1 Capacity has many project day Assignments.

That will allow you to summarize up to capacity to see where you have unused or overused capacity.

I would first get this working manually, and then we can discuss ways to make the process of adding the assignment records more easily. A stating point is that for the drop down record to choose a capacity day to assign a qty to, to filer the drop down report for only where there is some capacity remaining.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

Forum Discussion

List days capacity between two dates

Related Content

Distinct Dates

Formula Date Field

Date Field Formula

Historical Date Change Tracking

Calculating number of days between two date fields