Recurring Tasks Calendar

[udelgadochio]

Hello you all!

I've been looking for a way to automate a calendar that I use and as of today I fill it manually.

It is a calendar of recurring tasks but they change through the weeks of the year.
For example I have tasks that are on mondays every 3 weeks.
Others that are monday, wednesday and friday every 2 weeks.
Others on tuesdays every week... and so on.

What I want to show is only the tasks for the current week but previously calculated so that I don't have to input them.

Is there any easy way to do this? I've spinning on my head several days by now and can't find the solution...

 

[RasGhul]

Here's a possible formula solution if you can use it. The idea is to create bulk batches of tasks and copy them into a table.

The formulas will always show current week and will dynamically update from the TaskTbl data.


https://www.dropbox.com/s/8cv2m55rjlbrs2h/Recurring Task's Calenar_udelgadochio.xlsx?dl=0

 

[SunnyD11]

@RasGhul is there any way you could repost your code. I'm having this exact same dilemma and any assistance would be so appreciated!

 

[RasGhul]

Hi Sunny,

Note the Appointments table is an excel table.

Calendar Sheet_new.xlsm
	A	B	C	D	E
1	Date	Client	Notes	Mobile	Next Booking
2	01/08/21	Task1
3	01/08/21	Task1
4	01/08/21	Task1
5	01/08/21	Task1
6	03/08/21	Task1
7	03/08/21	Task1
8	03/08/21	Task7
9	03/08/21	Task8
10	07/08/21	Task9
11	07/08/21	Task10
12	07/08/21	Task11
13	07/08/21	Task12
14	07/08/21	Task13
15	07/08/21	Task14
16	07/08/21	Task15
17	07/08/21	Task16
18	18/08/21	Task17
Appointments

Calendar Sheet_new.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	Today's Date	31/08/2021
3							Aug 2021
4
5	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday		1st of Month	1/08/2021
6	1/08/2021	2/08/2021	3/08/2021	4/08/2021	5/08/2021	6/08/2021	7/08/2021		Weekday	1
7	Task1		Task1				Task9		Month 1	8
8	Task1		Task1				Task10		Month 2	9
9	Task1		Task7				Task11		EOM Month 2	30/09/2021
10	Task1		Task8				Task12
11							Task13
12							Task14
13							Task15
14							Task16
15
16
17	8/08/2021	9/08/2021	10/08/2021	11/08/2021	12/08/2021	13/08/2021	14/08/2021
Calendar

Cell Formulas
Range	Formula
B2	B2	=TODAY()
G3	G3	=G6
A5:G5	B5	=B6
A6:G6	B6	=COLUMNS($A$6:B6)-$J$6+$J$5
A7	A7	=IF(MONTH(A$6)<>$J$7,"",IF(ROWS(A$7:A7)>COUNTIFS(Salesdata[Date],A$6),"",INDEX(Salesdata[Client],SMALL(IF(Salesdata[Date]=A$6,ROW(Salesdata[Date])-ROW(Salesdata[#Headers])),ROWS(A$7:A7)))))
J5	J5	=EOMONTH($B$2,-1)+1
J6	J6	=WEEKDAY(J5,1)
J7	J7	=MONTH(J5)
J8	J8	=MONTH(EOMONTH($J$5,0)+1)
J9	J9	=EOMONTH(J5,1)
A17	A17	=G6+1
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6:G71	Expression	=MONTH(A6)<>$J$7	text	NO