Hello! How add formula date picker (date range) in column?
I have 2 tables. One table maintains a list of tasks with a start and end date.
In the second table, I want to make a pivot table with columns that would be a date range.
For example, there is a column with a range from April 13 to April 17, and she would check the first table and display all the tasks that were at that time.
The formula I’m trying to do is:
[Tasksdatabase].Filter([Date start].Contains(First(toDate(“4/6/2020”)),Last(toDate(“4/13/2020”))))
Now the list gives only 2 dates, but I need a range
I do not need to do it through a filter, but I need a table with a breakdown.
Shareable Document:
Hi @Evgeniy_Mamontov,
The quick version:
To select the range of dates you could use comparisons: [Table 1].Filter([Date start] >= toDate("4/6/2020") && [Date end]<= ToDate("4/13/2020"))
The long version:
I added a section to your doc starting with same data from Table 1, called (with a twist of originality) Data where you can see a Week calculated column.
This way you can have a dynamic “pivot” view by just grouping by Week. (there are some intermediate hidden column so that you can rebuild the underlying logic)
My only question is: what you would like to do when you have multiple weeks?
I hope it helps: please let me know if I correctly got your point
Cheers!
1 Like
Hi! @Federico_Stefanato!
Oh thanks! Yes, indeed it was possible to do with a simple formula more or less)
Sometimes in Coda solution formula is not obvious, but the problem-solving experience is amazing)
Your section looks as very dynamic & adaptive solution, nice bonus solution for my question, thank you very much for your help!) 
Multiweek looks like an interesting opportunity for long sprints) 
If the task lasts several weeks, I would like the columns to be automatically added exactly every weeks in which it participates
@Federico_Stefanato
Do you think this will affect performance, if this automatic “pivot” counts about >1,500 tasks?
@Evgeniy_Mamontov
I’m happy it was helpful!
This is a bit trickier…
Are we sure that this data model design is the more efficient?
Did you have the chance to have a look at some good starting points with Coda Templates (here some specifically on sprint planning: Gallery | explore Coda docs, templates, and Packs - Coda).
Check it out and see if some of those could be better bended to your needs.
In case, let me know
I’m pretty confident that this should not affect performances: there are not so complicated formulas and it’s basically a grouping view.
You can however test it with some data and see if you perceive some performance issues.
Btw, do you know you can also activate active monitoring and debugging?
@Federico_Stefanato
Yes, I know about debugging performance document)
Yes, the data model design is needed as a summary of all tasks divided by weeks, a kind of calendar report
I am aware of the gantt chart and life hacks with date range filters, but it doesn’t quite suit us.
I could manually create 52 Week columns and manually set my own date range in each, but it doesn’t look very elegant)
Ok, let me know if you need further help.
Enjoy!