Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Display entries based on date interval

Copper Contributor

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

 TASKstart dateend date
1task101-Jan01-Jan
2task201-Jan09-Jan
3task303-Jan07-Jan
4task404-Jan04-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

TASKASSIGNED DATE
task101-Jan
task201-Jan
task202-Jan
task203-Jan
task204-Jan
task205-Jan
task206-Jan
task207-Jan
task208-Jan
task209-Jan
task303-Jan
task304-Jan
task305-Jan
task306-Jan
task307-Jan
task404-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

4 Replies

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

power query.png

@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!
best response confirmed by alecsi (Copper Contributor)
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".

tasks.png

 

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

Wish you all the best!
Alecsi
1 best response

Accepted Solutions
best response confirmed by alecsi (Copper Contributor)
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".

tasks.png

 

View solution in original post