Forum Discussion

BBS90's avatar
BBS90
Copper Contributor
Mar 05, 2024

Automatically generate list of activities based on periodicity and time frame.

Hello Guys

I am trying to generate a spreadsheet for all the activities to be done for an item within a specific period. 

In Table 1, I have item, activity, and periodicity.

So in Table 2 needs to be populated table with the list of all activities to be done for an item i.e based on the periodicity (monthly, biannually, etc) and time frame (2024 or 1 year), 

 

I have simplified the task for this example, in the real case I have 100+ items and the periodicity varies from daily to annually. 

 

Thanks in advance for your support

TKS

 

excel
Formulas and Functions

1 Reply

Sort By
  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Mar 23, 2024

    Hi BBS90

    I have put together a function for you. However, it probably only works with Excel 365.
    I have added daily, weekly and yearly to the formula. Try it out to see if it comes close to what you want.

    It's probably easier too. But I didn't think of that. Maybe this will inspire someone.

     

     

    =LET(
sda,C2,
eda,C3,
mylist,A5:D10,
mo,(MONTH(eda)-MONTH(sda))+(--DAY(eda)>DAY(sda))+(TRUNC(YEARFRAC(sda,eda-1,3))*12),
zei,ROUNDUP(SWITCH(INDEX(mylist,0,4),"annually",mo/12,"biannually",mo/6,"quarterly",mo/3,"Monthly",mo,"weekly",(eda-sda+1)/7,"daily",eda-sda+1,""),0),
ste,SWITCH(INDEX(mylist,0,4),"annually",12,"biannually",6,"quarterly",3,"Monthly",1,""),
SORT(HSTACK(
TOCOL(MAKEARRAY(MAX(zei),COUNTA(zei),LAMBDA(ze,sp,IF(INDEX(zei,sp)<ze,#N/A,INDEX(mylist,sp,1)))),3,TRUE),
TOCOL(MAKEARRAY(MAX(zei),COUNTA(zei),LAMBDA(ze,sp,IF(INDEX(zei,sp)<ze,#N/A,INDEX(mylist,sp,2)))),3,TRUE),
TOCOL(MAKEARRAY(MAX(zei),COUNTA(zei),LAMBDA(ze,sp,IF(INDEX(zei,sp)<ze,#N/A,INDEX(mylist,sp,3)))),3,TRUE),
TOCOL(MAKEARRAY(MAX(zei),COUNTA(zei),LAMBDA(ze,sp,IF(INDEX(zei,sp)<ze,#N/A,INDEX(mylist,sp,4)))),3,TRUE),
TOCOL(MAKEARRAY(MAX(zei),COUNTA(zei),LAMBDA(ze,sp,IF(INDEX(zei,sp)<ze,#N/A,SWITCH(INDEX(mylist,sp,4),"weekly",sda+((ze-1)*7),"daily",sda+(ze-1),EDATE(sda,(ze-1)*INDEX(ste,sp)))))),3,TRUE)
),5))

     

     

Resources