Display entries based on date interval

[alecsi]

Hi guys,

Need your help with this one. I could not attach the xlsx file here, so I uploaded on wetransfer link: 

https://we.tl/t-s8sAL5y95g

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

[#2]

@alecsi 

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.

[#3]

@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!

[#4 · Accepted solution]

@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".

[#5]

@OliverScheurich Thank you so much!!
This is excellent!!! solved my problem.

Wish you all the best!
Alecsi