How to use filter table with a drop down that doesn't relate directly?

I am making a time tracker for my different tasks. I want to be able to filter the table using a drop down menu to see how much time I have spent today, this week, this month, and all time. but I don’t know how to do that…

Here is the table with all the tasks that I need to filter:

And here is the page I want to link the resulting info to (just above the blue lines):

Ideally, I would use this to eventually see that I get weekly goals done of doing a certain number of hours per task, but first I need to learn how to filter the tasks, preferably by both the task and the day, separately. Aka, have a drop down menu picking the task I want to see the hours of (including an ‘all’ option), and another drop down to pick the date range I want to see.

I hope I explained that okay.

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

  1. looking at the Calendar table,
  2. 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)
  3. 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).



I also found that simply filtering new linked table, and then getting data from that helps keep everything organized.

1 Like