Forum Discussion
Display entries based on date interval
Hi guys,
Need your help with this one. I could not attach the xlsx file here, so I uploaded on wetransfer link:
I have two tables: Table A and Table B
Table A can have maximum 70 entries.
Table B can have maximum 280 entries.
I insert in Table A the task, the start date and the end date
TABLE A
| TASK | start date | end date | |
| 1 | task1 | 01-Jan | 01-Jan |
| 2 | task2 | 01-Jan | 09-Jan |
| 3 | task3 | 03-Jan | 07-Jan |
| 4 | task4 | 04-Jan | 04-Jan |
and I need a formula that displayes in Table B all of those entries separately
Example:
task 1 is assigned for only one day = it will appear once in the list on 01-Jan
task 2 is assigned for a period of 9 days in total, so it will appear 9 times in the list, each time with a different date (from 01-Jan to 09-Jan)
... and so on
TABLE B
| TASK | ASSIGNED DATE |
| task1 | 01-Jan |
| task2 | 01-Jan |
| task2 | 02-Jan |
| task2 | 03-Jan |
| task2 | 04-Jan |
| task2 | 05-Jan |
| task2 | 06-Jan |
| task2 | 07-Jan |
| task2 | 08-Jan |
| task2 | 09-Jan |
| task3 | 03-Jan |
| task3 | 04-Jan |
| task3 | 05-Jan |
| task3 | 06-Jan |
| task3 | 07-Jan |
| task4 | 04-Jan |
It must work this way because this is an additional feature that I need to add to an excel sheet that already exists for some time. So, there is no much room to change things
Please help, I can't find any solution to this
Best regards,
Alecsi
=DROP(REDUCE("",SEQUENCE(ROWS(Tabelle7[TASK])),LAMBDA(x,y,VSTACK(x,HSTACK(IFERROR(EXPAND(INDEX(Tabelle7[TASK],y),INDEX(Tabelle7[end date],y)-INDEX(Tabelle7[start date],y)+1),INDEX(Tabelle7[TASK],y)),SEQUENCE(INDEX(D2:D5,y)-INDEX(Tabelle7[start date],y)+1,1,INDEX(Tabelle7[start date],y),1))))),1)
With Office 365 or Excel for the web you can create a dynamic table and apply this formula. In my example the table name is "Tabelle7".
You can use Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
- OliverScheurich Thank you for your answer.
Unfortunately this does not help me because everything needs to be executed automatically. Meaning, no manual refresh can be done.
Do you think that there is a formula that can do this? It would be very helpful to get any suggestions. Many thanks!
=DROP(REDUCE("",SEQUENCE(ROWS(Tabelle7[TASK])),LAMBDA(x,y,VSTACK(x,HSTACK(IFERROR(EXPAND(INDEX(Tabelle7[TASK],y),INDEX(Tabelle7[end date],y)-INDEX(Tabelle7[start date],y)+1),INDEX(Tabelle7[TASK],y)),SEQUENCE(INDEX(D2:D5,y)-INDEX(Tabelle7[start date],y)+1,1,INDEX(Tabelle7[start date],y),1))))),1)
With Office 365 or Excel for the web you can create a dynamic table and apply this formula. In my example the table name is "Tabelle7".
- OliverScheurich Thank you so much!!
This is excellent!!! solved my problem.
Wish you all the best!
Alecsi
