Forum Discussion
Pull data between two dates from multiple tables
I am trying to filter between two dates based on the start and end dates of classes. I can't figure out how to sort through different tables and the formula I was given doesn't pull the exact dates that I need. I want to be able to add classes as time goes on, so the sheet should be able to update based on the two dates at the top of the sheet 'Snapshot'. As of now, it should be pulling class [A-001 - A-004], [B-002 - B-010], and [C-001 - C-010].
I cleaned up the formula in A7 visually (splitting it across multiple lines), but left it essentially unchanged, to make it easier to understand. The main problem is that it relies on the WEEKNUM function (calculating the week of the year), which would make sense if individual classes did not cover multiple calendar years. But as they do, a different technique is needed.
I believe what you want is this formula, in cell A21 in the attached workbook:=LET(tasks, SORT(VSTACK(TaskA, TaskB, TaskC), {1,2}), start_dates, TAKE(DROP(tasks, , 1), , 1), end_dates, TAKE(tasks, , -1), period_start, calendar_start, period_end, $D$1, FILTER(tasks, (start_dates <= period_end) * (end_dates >= period_start), "none") )When you have tested that and are satisfied with the results, replace the formula in A7 with the formula in A21, and delete the latter.
Some other changes I made (such as to row/column widths and to how Snapshot's freeze panes are defined) are inconsequential, and you can change them as you wish.
Also:- The End Date for class C-007 looks like a date value, but is actually stored as text (despite the Date formatting; compare the values shown in the formula bar for that cell and the cell beneath it). I verified that using a formula with the TYPE function. I'll let you review that cell's value and edit it as appropriate.
- The formula in D1 of Snapshot was unnecessarily complicated; I recommend instead
=calendar_start + 56although maybe you want to add 55 to cover exactly 8 weeks, instead of adding 56.
9 Replies
I cleaned up the formula in A7 visually (splitting it across multiple lines), but left it essentially unchanged, to make it easier to understand. The main problem is that it relies on the WEEKNUM function (calculating the week of the year), which would make sense if individual classes did not cover multiple calendar years. But as they do, a different technique is needed.
I believe what you want is this formula, in cell A21 in the attached workbook:=LET(tasks, SORT(VSTACK(TaskA, TaskB, TaskC), {1,2}), start_dates, TAKE(DROP(tasks, , 1), , 1), end_dates, TAKE(tasks, , -1), period_start, calendar_start, period_end, $D$1, FILTER(tasks, (start_dates <= period_end) * (end_dates >= period_start), "none") )When you have tested that and are satisfied with the results, replace the formula in A7 with the formula in A21, and delete the latter.
Some other changes I made (such as to row/column widths and to how Snapshot's freeze panes are defined) are inconsequential, and you can change them as you wish.
Also:- The End Date for class C-007 looks like a date value, but is actually stored as text (despite the Date formatting; compare the values shown in the formula bar for that cell and the cell beneath it). I verified that using a formula with the TYPE function. I'll let you review that cell's value and edit it as appropriate.
- The formula in D1 of Snapshot was unnecessarily complicated; I recommend instead
=calendar_start + 56although maybe you want to add 55 to cover exactly 8 weeks, instead of adding 56.
- That seems to work how I would like it to. How would I add more arrays to that formula and do the arrays you have in the formula extend to A:A, B:B, etc? When I try to add another array, it comes up with a #NUM error
The arrays in that formula are defined just as in the workbook you supplied; I made no changes there. As currently defined in Formulas → Name Manager, each array can have up to 999 rows (worksheet rows 2 thru 1,000):
and that number of rows can be readily expanded as needed. (You are not required to use dynamic named arrays such as TaskA, TaskB, TaskC, but consistency is nice.)
For help with your #NUM error, you'll have to show your work. Either attach your updated workbook (or a subset thereof) or post the formulas you now have in the Snapshot column A cell and for any names you added to/changed in Name Manager.
