Can I get a list of all dates between 2 date columns?

Hi, I want to search for records in my list from a specific duration.

For example, I have 10 tasks assigned from February 1st to February 14th. Each task has a column that shows how many units I have completed.

I have made a separate sheet and tab for analysis, made two new columns for (DATE FROM) and (DATE TO). I added an inline list of tasks to that tab and filtered it through dates.

The problem is, I want to use the ROLL UP FEATURE TO SUM THE NUMBER OF UNITS I HAVE COMPLETED DURING THAT DURATION. How do I do that?

Is there any way I can get a list of all dates between the dates I have selected to search in a joined list, I split text them and make a relation of them with task sheet?

This is an excellent use case for this request:

Your description is probably a lot simpler than mine.

BUT!!!, I think in your case, it’s still achievable since it sounds like a case of one set of From/To dates. In my case, I have hundreds or thousands of rows that need to find a related set of data within a date range that varies for each of the hundreds/thousands of rows. So in reality, my situation seems different from yours.

However, to achieve what you want, if you can add single value columns to your task table, then you can populate the From and To date column value across all rows using single value columns. Then with an IF column, you can run a comparison to see if each row fits within that range and return the number of units if it’s within range, or return zero if it’s not. Then you can rollup the result of that IF column. That way it will only count the units within that date range.

4 Likes

Thank you for this idea. Will try this and get back to you.


So I tried your trick but I am stuck on how to use IF ELSE COLUMN using two separate date columns. Wish there was an option of using “and” and “or” in glidetables, so I would have done:

If “PRODUCTION ASSIGNED DATE” is on or after “DATE FROM COLUMN” and “PRODUCTION ASSIGNED DATE” and is on or before “DATE TO COLUMN” then show “Number of units”. and then I would have rolled up that value to get sum and count.

Do it like this:

  • If PRODUCTION ASSIGNED DATE is before DATE FROM COLUMN, then “nothing” (leave it empty)
  • If PRODUCTION ASSIGNED DATE is after DATE TO COLUMN, then “nothing” (leave it empty)
  • Else Number of units

Then you can do your rollup on that.

2 Likes

Yes, I just tried that and it worked! You guys are amazing!!

1 Like

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.