Forum Discussion
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...
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))