Dynamic Task Schedule

[EvenS8245]

I’m not sure this is even possible but I’m hopeful, so it will save me time.



I have been tasked with creating a calendar for updating scheduled tasks at work. These dates are constantly updated to meet business needs. I’m trying to write a formula that would pull these dates and update on the calendar that I have built so I don’t have to manually delete the previous date and update the new date.

I have built a calendar which will refresh when I select a different year (using a drop down list). The dates will refresh by using a sequence formula written out as =SEQUENCE(1,7,$A$1-WEEKDAY($A$1)+1) – the following weeks in the month would be the formula +7,+14, etc. I included a conditional format formula =MONTH(B8)<>MONTH($A$1) to grey out the dates that occurred in the sequence but did not fall in the month.



There are nine different types of records that could show up on a scheduled date (sometimes 2 could be scheduled the same date), so I have left nine available rows (one for each task type) for each date.

My guess is that I would need some type of IF with an Index formula to show the scheduled type for the date or possibly an IFNA or IFERROR. I'm also guessing that i can use the same conditional format formula to leave the cell greyed out if it doesn't fall within the month.



Does anyone know if this is just crazy or if it is possible to have the tasks populate on the date they occur?

Sorry i'm not able to upload mini-sheet. keeps giving me an error.

	May	2023		June	2023
	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
	30	1	2	3	4	5	6		28	29	30	31	1	2	3
Introductions
Notes
Projects
Videos
Meetings
Trainings
Staffing
Adjustments
Presentations
	7	8	9	10	11	12	13		4	5	6	7	8	9	10
Introductions
Notes
Projects
Videos
Meetings
Trainings
Staffing
Adjustments
Presentations
	14	15	16	17	18	19	20		11	12	13	14	15	16	17
Introductions
Notes
Projects
Videos
Meetings
Trainings
Staffing
Adjustments
Presentations
	21	22	23	24	25	26	27		18	19	20	21	22	23	24
Introductions
Notes
Projects
Videos
Meetings
Trainings
Staffing
Adjustments
Presentations
	28	29	30	31	1	2	3		25	26	27	28	29	30	1
Introductions
Notes
Projects
Videos
Meetings
Trainings
Staffing
Adjustments
Presentations

Here is the raw data i have for the task schedule:

2023
Introductions
May	June	July	August	September	October	November	December
3-May	2-Jun	4-Jul	2-Aug	5-Sep	3-Oct	2-Nov	4-Dec
4-May	3-Jun	5-Jul	3-Aug	6-Sep	4-Oct	3-Nov	5-Dec
5-May	4-Jun
	5-Jun
Notes
May	June	July	August	September	October	November	December
8-May	6-Jun	6-Jul	4-Aug	7-Sep	5-Oct	4-Nov	6-Dec
9-May	7-Jun	7-Jul	5-Aug	8-Sep	6-Oct	5-Nov	7-Dec
Projects
May	June	July	August	September	October	November	December
9-May	9-Jun	9-Jul	8-Aug	8-Sep	9-Oct	6-Nov	7-Dec
Videos
May	June	July	August	September	October	November	December
11-May	11-Jun	11-Jul	10-Aug	11-Sep	11-Oct	10-Nov	7-Dec
Meetings
May	June	July	August	September	October	November	December
10-May	10-Jun	10-Jul	10-Aug	11-Sep	11-Oct	10-Nov	6-Dec
Trainings
May	June	July	August	September	October	November	December
17-May	16-Jun	16-Jul	18-Aug	18-Sep	18-Oct	15-Nov	16-Dec
Staffing	EMEA / APAC
May	June	July	August	September	October	November	December
15-May	14-Jun	13-Jul	14-Aug	13-Sep	13-Oct	15-Nov	8-Dec
Adjustments
May	June	July	August	September	October	November	December
22-May	21-Jun	20-Jul	22-Aug	20-Sep	20-Oct	17-Nov	18-Dec
Presentations
May	June	July	August	September	October	November	December
23-May	22-Jun	21-Jul	23-Aug	21-Sep	23-Oct	20-Nov	19-Dec

 

[EvenS8245]

I created a pivot table for the months and dates. thought that would be easier

Introductions
Row Labels	Max of Day	Max of Day2	Max of Day3	Max of Day4	Max of Day5
May	5/3/2023​	5/4/2023​	5/5/2023​
June	6/2/2023​	6/3/2023​	6/4/2023​	6/5/2023​
July	7/4/2023​	7/5/2023​
August	8/2/2023​	8/3/2023​
September	9/5/2023​	9/6/2023​
October	10/3/2023​	10/4/2023​
November	11/2/2023​	11/3/2023​
December	12/4/2023​	12/5/2023​
January	1/4/2024​	1/5/2024​	1/6/2024​

 

[EvenS8245]

Solved with COUNTIF

 

[Peter_SSs]

Good to hear you solved it.
If you would like to post the full solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[EvenS8245]

I converted the dates into a fixed table and named the table "DD".

Then wrote out a count if formula with a reference to the date. Of course this formula had to be modified for each row to search different tables.

=IF(COUNTIF(DD[#All],E144),Ref!$H$3,"")

Seemed to work. Now i need to modify to only pull back if it falls within the listed month.