A ready-to-use Microsoft Excel Template for a dynamic Project Calendar, displaying regular Project Meetings and Deadlines for one selected week
As per the subtitle of this blog (see above), Clearly and Simply is about data analysis, modeling, simulation and data visualization.
If you are a long time reader of this blog (and I am aware there are only a handful), you know that – from time to time – I also post something about Project Management.
I already published posts here about
- interactive checklist templates (Microsoft Excel Check List Template and Microsoft Excel Check List Compilation)
- how to export tasks from Microsoft Project to Outlook
- how to import tasks from a Microsoft Project file into Excel
- how to create a Gantt Chart in Microsoft PowerPoint from a Microsoft Project file.
Following this tradition, today’s post provides another little piece out of my Project Management Toolbox: a ready-to-use, dynamic Project Calendar Template in Microsoft Excel. Of course, including the template for free download.
A Dynamic Project Calendar
The goal is to visualize all relevant meetings (like Steering or Project Boards, Project Team meetings, daily Scrum calls, etc,) and deadlines (like delivery dates of pre-reading documents) of a project in a weekly overview:
The user selects a week using the spin button at the top and will see all relevant appointments and deadlines of this week. Today and the current time will also be highlighted.
What do you need it for?
You may ask: “why would I need a template showing all regular project meetings, if I can simply look into my Outlook calendar?”
Well, for several reasons:
- A project calendar should be one part of your overall project documentation and is especially helpful when your project will undergo an audit or review
- Not all project members will attend all regular meetings, i.e. they will not have all appointments and deadlines in their calendars. The Project Calendar can be a useful reference for them to see what happens when and what has to be delivered when
- The Project Calendar is very helpful when initially setting up the meeting structure of an upcoming project to see possible clashes of appointments and to define deadlines for e.g. the delivery of pre-reading documents
- It is also very useful when onboarding new project members
How to use the Template
The process of using this template for your own project is pretty simple:
Step 1: Download and open the Excel workbook (download link see below)
Step 2: Go to the worksheet [Control] and define the start date of your project in cell C4. Select a Monday, if you want the calendar view to show the week from Monday to Sunday (the view Europeans are used to) or a Sunday, if the view shall display the week from Sunday to Saturday (usually preferred in the US)
Step 3: Go to the worksheet [Calendar Data] and delete everything inside the table right to the column [ID]:
Now insert your own data. The table structure is very simple and should be self-explanatory. Just 2 hints:
- For weekly and bi-weekly items, the weekday of [Start Date] defines the weekday of this meeting or deadline.
- In column [End Date] you can define, from when on this meeting or deadline will not be relevant anymore. For the items running throughout the entire project, you define the end date of the project as the [End Date]. For temporary meetings, e.g. like meetings within a 2-weeks sprint, you set the end date of this meeting series to the date of 2 weeks after the start date.
The Implementation
Actually, this is a very simple Excel workbook. No VBA included, only a few formulas. Having said that, the calculation inside the value area of the weekly view is a pretty complex array formula.
I will not explain this formula here in detail.
If you only want to use the template in your project, you do not necessarily have to understand how it works. Just be aware that it is an array formula which needs to be entered by CTRL-Shift-Enter. To avoid unintentional changes of the formulas, I recommend to protect this sheet.
If you are an advanced Excel user and interested in how it works, have a look at it for yourself and let me know any questions you might have in the comment section. If you find a better (i.e. shorter and easier to understand) formula to achieve the same output, please let me know.
Download the free Microsoft Excel Project Calendar Template
Download Project Calendar Template (zipped Microsoft Excel workbook, 23K)
Stay tuned.
Leave a Reply to Josh Cancel reply