Creating a Dynamic Calendar

Overview

This is a sample Excel workbook which takes dates and events in a list, color codes them, and exports them into a natural and printable calendar format. It is designed to take events with durations and dependencies (as in a Gant Chart), and to update them quickly.

The downloadable link is here.

Sample Project

The example in this workbook is a project with 5 sub-projects. Each project is about 1 week long, with a review scheduled for one day after completion and associated documentation due within 3 days of completion.


Sample Input


Results for Month of May

Background and Discussion

Some time ago, I was tasked with helping to manage an Agile development. In addition to the Sprints, I was tasked with keeping track of developing internal documentation, filing approvals for release, and developing detailed updates to management, including long-term and financial planning.

I had about 150 sub-tasks associated with this project, and they kept shifting around. Sometimes a meeting would get pushed back or canceled, or we would find a document that we needed to complete before accepting the code, or the sprint itself would be delayed.

On top of all this, the client needed a quickly digestible and printable calendar so they could plan for the upcoming weeks. I came up with the concept then and am sharing now a new version of the solution - an Excel sheet that is easy to list out many tasks and automatically color codes and publishes them to a printable format.

There are several advantages to this sheet.

  1. Format of dates lends nicely to integration with existing Gantt charts 
  2. Start dates can be linked to finish dates so that one update will cascade through the calendar
  3. Grouping broad workstreams (such as accounting, documentation, product releases) can be done naturally so that they can be quickly identified on the primary Task List sheet
  4. The font color that shows up in the calendar will be the same as it is shown in column B in the Task List, so this can be infinitely customized

While it does not presently exist, I plan on adding functionality later to export the events here to Outlook's built-in Calendar.


Quick use note - when entering a new date, try to insert the rows since the script reads down Task List sheet columns C-E to find dates, and will only go as far as the 'Max Rows value', shown in Row 1. Otherwise, the sheet is not very flexible to moving cells around.


Since there are some interesting code solutions in here, I will tag this in case anyone is looking for these:

Excel VBA
Insert Text
Add multi-colored text
Add text with different colors
Add text preserve formatting
Detect text formatting
Create calendar template
Color code

Comments

Post a Comment

Popular posts from this blog

What does it take to win at Kaggle? An Introduction to Data Strategy

Image
For our latest project, we were set to work on a completed  Kaggle competition  and tasked with analyzing the appearance of West Nile Virus (WNV) in Chicago to see if we could identify where the virus would start. Having learned my lesson from my experience with Ames housing prices , and being given a completed Kaggle leaderboard to start with, I wanted to know what constituted a 'good' score, and how hard it would be to get there. Hoping for a nice clean graph, I took a look at the score submissions over time: This is obviously a mess. If you look closely, there are some red, yellow, and green data points in there. These are respectively scores from groups with: The lowest 10 scores on the board Scores closest to the average Scores in the top 10 overall These are isolated here: Rolling over will isolate the groups. There seems to be a general elbow curve up and to the right, with groups slowly getting closer to a max score ...
Read more

Cutting Logs Part 1: Catching Ideas and Z's

Image
Trouble Sleeping Along with many other Americans, I often have a little trouble sleeping after a long day of staring at computer screens. My favorite solution at the moment is to take melatonin supplements. Like a lot of vitamins, it's a very iffy method with no regulation on dosage size or measure of effectiveness. Thus, while the limited scientific material I could find suggests between .5-1.5 mg for consumption, the smallest size I can find in the store comes in 3 mg. My solution for this has been to cut the pills in half, and throw the unused half back in the bottle. This went well until the other week, when I began to be bothered by the sensation that I only seemed to ever pull full pills from the bottle, despite having them around for months. So, I did what any normal person does with a vague but quantifiable suspicion and built a model to study the concentration of whole and half pills in the bottle over time, and the subsequent likelihood of pulling one or the other. ...
Read more