I have an imperfect solution that hopefully starts you in the right direction.
Here is my setup:
The table is similar to your table, however, I have renamed the first column to Task and used generic placeholder names for the tasks.
Next, I setup a Select control, which you can find here:
This creates a freestanding input field. We next want to limit the selection options to those tasks which are listed in the Task column of the Calendar table, which can be done by inputting the following formula in the settings of the Select control:
In addition to limiting the selectable options, it will dynamically change as new tasks are added to the Calendar table.
Next we will insert a Date range control. This can be found in the same place as the Select control, and is the option directly above it (“Date range”).
You can give custom names to these controls, which is important for referencing them in formulas. Please note that I renamed my Date range control to daterangepicker1 (this was the default name but without the space before the 1) and the Select control to Taskpicker.
Finally, we can setup the output. I typed Output: (that part is optional) then inserted a formula. Typing = in a blank space in document will bring up the formula controls, and I inserted the following:
Calendar.Filter(Task=Taskpicker AND daterangepicker1.max()+1>=Stop AND daterangepicker1.min()<=Start).Duration.Sum()
What is this formula doing? It is
- looking at the Calendar table,
- filtering out items to only calculate rows/fields whose task equals the task specified in the Select control AND whose Start date is after or equal to the first date of the Date range control AND whose Stop date is before or equal to the end date of Date range control. (Note: I included a +1 for consistency, otherwise, for example tasks starting on the first day of the date range would be included, but tasks stopping on the last day of the date range would not be included)
- Summing the durations of the rows/fields remaining after the filters were applied.
Some caveats - There is no “All” option with this method, but that should be doable. Also, there may be a simpler approach with different formulas, but some of them did not work as I anticipated (e.g. Contains() for the dates).
