Populate dynamic calendar with tasks based on task frequency

**~Rebecca~** · 09-20-2021, 09:44 PM

Hi,

I am attempting to build a dynamic calendar in worksheet two that automatically populates tasks, based on the frequency of the task, from the table in worksheet one.

Day one, week one should be the first Monday of the month. Quarterly should be January, April, July and October. Biannually should be March and September. Some days will have multiple tasks. No tasks fall on a Sunday, Wednesday or Saturday.

I am unsure of the correct formula to populate the calendar based on the frequency criteria and need some help.

I have attached my workbook with sample data.

Thank you in advance!

Rebecca

**JohnTopley** · 09-21-2021, 02:35 AM

As the calendar requires a reference per event, then the the "Daily" (Monday/Tuesday/Thursday/Friday) will require 4 entries. Similar logic is required for Odd/Even Weeks and Months.

A possible answer is to use VBA to generate an extended tasks list [i.e. "convert" daily into 4 references] which meets the needs of the calendar.

.

**kvsrinivasamurthy** · 09-21-2021, 05:43 AM

Some doubts . I have put in Tasks sheet.
The format in Sheet1 for calendar is ok.

**JohnTopley** · 09-21-2021, 05:56 AM

From the OP ..

Quarterly should be January, April, July and October. Biannually should be March and September.

**kvsrinivasamurthy** · 09-21-2021, 06:13 AM

Thanks JohnTopley. What about the format of calendar. Let us wait.

**JohnTopley** · 09-21-2021, 06:28 AM

Calendar format is set and event entry in calendar is determined by the Reference which is Date plus underscore "Event number".

e.g 2 events on 5th January 2021 will have the following references

44201_1
44201_2

Example attached (originator is Pete_UK)

**~Rebecca~** · 09-21-2021, 07:18 AM

Hi John, thank you for your suggestions. I will look into building a VBA.

**JohnTopley** · 09-21-2021, 07:25 AM

I will look into building a VBA

Do you mean YOU will do the VBA OR do you still want a response from the forum?

If you agree a VBA solution is needed, then this thread should be moved to the VBA/Macro forum. A Moderator can do this for you.

**kvsrinivasamurthy** · 09-21-2021, 07:33 AM

Is it possible to use the the calendar format I have given in my previous post.

**JohnTopley** · 09-21-2021, 07:46 AM

Thinking aloud, your "Sheet1" could be a possible basis for generating the "Activities" sheet needed by this calendar application. Caution that the number of tasks will grow!

All the formulae in calendar reference the "Activities" sheet which should remain the prime source of input.

I produced an "Actvities" sheet some time ago with VBA but it was a somewhat simpler series of events.

**kvsrinivasamurthy** · 09-21-2021, 07:58 AM

What is Op's opinion.
Pl see enclosed file.

**~Rebecca~** · 09-21-2021, 08:04 AM

Thank you. I will have a go at writing a VBA script to generate the tasks into an "Activities" worksheet that the calendar can then reference. I can start a new thread in the VBA/Macro forum if I get stuck and need help or would it be better to have this thread moved there?

**JohnTopley** · 09-21-2021, 08:52 AM

@KVS
I am not sure the format will work as the "Tasks" are "Type of Tasks" (based on frequency) but my understanding is there can (will!) be multiple "activities" for a given "Type of Task" i.e. every Monday: I visit the Supermarket, do my washing, play Darts in the Evening so 3 activities.

Unless you are using your table as a "lookup" to match activities with the Task frequencies then the above does not apply [so your solution will certainly work.]

Equally, I am sure some activities will be changed (added/deleted) so the process needs to be able to re-generate an updated list of activities: in some cases i guess this may be a simple manual change.

@Rebecca: can you confirm (or not!) the above analysis.

**kvsrinivasamurthy** · 09-21-2021, 09:35 AM

@JohnTopley
Op is not so explicit in his requirement. Anyway he has decided to go for VBA code. Thank You

**JohnTopley** · 09-21-2021, 11:13 AM

@Rebecca

Attached is my interpretation of task frequencies based on KVS's form. My Week assignments may be wrong as I get a "problem" with bi-annual in September (no Friday for Week 4).

The week column is manually entered.

Please also see post #13 re requirements. Thank you.

Second file has Week based on 1 st Monday of each month

**~Rebecca~** · 09-21-2021, 06:22 PM

Hi John,

Visit the Supermarket, Do my Washing, Play Darts in the Evening, may be entered as three separate tasks with each task occurring on day one (Monday), weekly (every week) and monthly (every month). So each of these tasks would then be generated on 48 occurrences over the course of a year.

Also, some activities may be changed, added or removed during the year.

**janmorris** · 09-21-2021, 06:33 PM

vertex42 has a very good calendar for free that allows you to input frequency based on all types of things. im not sure im allowed to share the link here but the one to look for is "Monthly Calendar with Holidays"

**kvsrinivasamurthy** · 09-21-2021, 11:14 PM

Try this. Different formulas are there for each task. You can add any number of tasks.

**kvsrinivasamurthy** · 09-22-2021, 01:47 AM

With slight change to avoid referring first row.
Month wise sheets can be done.
For new month copy the sheet. Change the date in A1
More tasks can be added.

Populate dynamic calendar with tasks based on task frequency

LinkBack

Thread Tools

Rate This Thread

Display

Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Re: Populate dynamic calendar with tasks based on task frequency

Thread Information

Users Browsing this Thread

Similar Threads

Dynamic interactive calendar populated by userform

Assigning tasks to team with each task having specific weights

Excel to record tasks done and assign a task to someone else

Distributing work across the calendar days based on effort taken per task

Chart only ONE task as percentage of ALL tasks? PLEASE HELP!

Put tasks into Calendar after my module generates the Calendar

Task Planner - read out list of tasks for different employees

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions