Quality check project plan on the fly with custom fields

I want to ensure that all tasks in my project plan have certain custom fields sets (like team lead, contract id, deliverable, etc.). To spot the tasks that lack this information, I’ll highlight them in my Gantt view.

First step, add a custom field, say, number18 and a formula that checks certain fields and returns

  • -1 for milestones and summaries,
  • 0 for all quality checks ok, and
  • a number greater zero if the quality check fails.
IIf([Milestone]=True;-1;IIf([Summary]=True;-1;IIf([Text7]="";1;IIf([Text15]="";2;IIf([Text27]="";3;IIf([Text28]="";4;IIf([Text16]="";5;IIf(InStr([Resource Phonetics];[Text16])=0;6;IIf([Type]<>2;7;
IIf([Baseline Finish]=projdatevalue("NA");8;0))))))))))

Second step, add a flag, say flag20 to be true if the quality check fails (number19 is greater 0) or false otherwise.


Third step, format the gant chart based on flag20.

How to create tag clouds for Evernote

Evernote’s current clients cannot create tag clouds, i.e. a visual representation of your data. Here are three simple steps to generate a tag cloud:

  1. Select all notes in Evernote for which you want to generate a tag cloud and export the data via File|Export|Export as a file in ENEX format to a folder on your hard drive, say c:\temp\Evernote.enex. Depending on your number of notes that file may be huge.
  2. Convert the exported file to something that only contains the tags.
    1. Convert the file with a command line utility from Microsoft (or any other XSL processor). Download msxsl.exe and save it to c:\temp.
    2. Download the XSL file which sets the conversion format and save it to c:\temp.
    3. Open a command window, go to c:\temp and use

      msxsl.exe Evernote.enex tagcloud-xsl.txt > tagcloud.txt

  3. Upload or copy/paste tagcloud.txt to a service that generates tag clouds.
  4. Your’re done.

In a future post  I will explain how to analyze your tag and possibly other meta data with Excel.

Yardage Map for Projects

I’m a passionate golfer and one thing I’ve learned in +20 years of play is that – besides solid ball striking – it pays off to know the course in advance. Be prepared. Based on your skills and clubs you can develop a strategy for the course. For each hole, you estimate distances and check hazards and the way the green is defended with bunkers. Eventually, you document your findings in a yardage map for later use.

What does all this have to do with project management, you ask? Well, this post is a first in a series of blog posts where I document my yardage map for projects – everything I need to successfully tackle projects in the IT industry.

My background

I’m currently employed both as a line manager and as a project manager for 7-figure contracts – teams of up to 30 employees. In the past, I’ve documented my tips and tricks in a number of places – files on my hard-drive, e-mails, and so forth. Over the years, this got too messy and I figured out this had to change. So why not publish my knowledge in a series of blog posts? My key idea is to list must-have or must-do items for each phase of a project for later reference.

The content

This blog is intended for project managers who look for practical tips and best practices. You will learn how to sharpen the scope of a project, create a proper project plan and even more importantly, how to automagically update the plan with minimal effort. An updated project plan is a key instrument for making sound decisions. For tracking projects, I use MS Excel and MS Project and you’ll learn a number of macros. For the impatient, check out the macro collection for MS Project or for Excel.

The upcoming posts are structured along PMI’s process groups which roughly correspond to the phases of a project.

  • Initiating
    • Shall I take over the job as project manager or not?
    • Use a project definition report to avoid failure.
  • Planning
    • The basics – what do I need?
    • How do I create a project plan?
    • Check your plan with this best practices checklist.
  • Executing, Monitoring and Controlling
    • The basics – what do I need?
    • The weekly cycle – how do I update the plan each week?
  • Closing
    • Say “Thank you”

This post serves as a table-of-contents for future posts. Stay tuned for more to come.

How to show trends in MS Project

In short, you can’t ;-). MS Project is good at showing all sorts of info on your project for any given point in time. It does not, however, contain historic data to calculate trends. But there is a workaround. In each reporting cycle (say every week), save your project plan to a new file. Then use Excel and a macro to read all past project plans and chart the trend. Use pivot tables to drill down to deliverable or sub-projects. Here is how to chart work compared to baseline work over time. Continue reading

How to import and export nonworking time in MS Project

Not reflecting holidays in project plans can have many nasty side-effects. Tasks end later than you think because people are not available. If you have longer projects, the number of holiday days, bridging days and other nonworking time add up and can significantly extend your project far beyond your established deadline.

Using “Tools | Change working time … ” in MS Project you can change both the project calendar and the calendar of each team member. However, this process is cumbersome. The following macros update nonworking time from an Excel file. Continue reading

Dump actual work from MS Project into Excel

As you may know, MS Project may occasionally behave divaesque. To make matters worse, older versions (like the one, I’m using) know only one (!) undo step. In order to check if some of my changes have screwed up actual work of the past, I dump all actual work from project start to project finish into an Excel file and analyze it with a pivot table. I can now easily compare actual work before and after I’ve made changes to the project. Continue reading

Set the same text styles in all views for MS Project

Running the following macro from your gantt-like views will standardize the view to use identical text styles.

' works only for gantt like charts
Sub swaMSPSetTextStyles()
    If ActiveProject.Views.Item(ActiveProject.CurrentView).Type = 0 Then
        TextStyles Item:=pjAll, Size:="8", Bold:=False, Italic:=False, Color:=pjBlack
        TextStyles Item:=pjNoncritical, Size:="8", Bold:=False, Italic:=False, Color:=pjBlack
        TextStyles Item:=pjGanttExternalTask, Size:="8", Bold:=False, Italic:=False, Color:=pjGray
        TextStyles Item:=pjMarked, Size:="8", Bold:=False, Italic:=True, Color:=pjNavy
        TextStyles Item:=pjTaskRowColumnTitles, Size:="8", Bold:=True, Italic:=False, Color:=pjBlack
        TextStyles Item:=pjGanttMajorTimescale, Size:="8", Bold:=True, Italic:=False, Color:=pjBlack
    End If
End Sub

How to add VBA code to your computer

First of all, all VBA examples on this website use MS Excel 2003 and/or MS Project 2003. Most of the stuff may run with later versions, YMMV. Also, keep an eye on formulas in Excel: you may need to replace each colon with a comma (or tweak your regional settings in Windows).

Add code to MS Excel

  • I store all my macros in a central place (personal.xls), so that I can use them in all spreadsheets.
  • On my Win7 box, the file is located in %APPDATA%\Microsoft\Excel\XLSTART
  • Read, how to add macros to your personal.xls.
  • Hide personal.xls when you’re not working on your macros (via Window | Hide).

Add code to MS Project

  • I store all my macros in a central place (global.mpt), so that I can use them in all projects.
  • On my Win7 box, the file is located in %APPDATA%\Microsoft\MS Project\11\1033
  • You can move macros and other settings between different project files and global.mpt using MS Project’s organizer function, see Tools | Organizer.

Don’t forget to backup both files on a regular basis.