I’ve used Microsoft Project in the past. There were some features I really liked, but mostly I found it frustrating, cumbersome and time-consuming. I hate wasting time; I have an obsession with being as efficient as possible. So a few years ago I ditched Project and decided to use Excel to handle my project management needs.

My solution is a combination of the =WORKDAY( formula and the stacked bar chart as a Gantt chart (as explained in this video and this post). In this post I’ll be talking about how I use the workday formula in Excel to calculate the key milestones for my projects all based off the due date and the number of days each activity will take.

For example: Let’s say I’m working on a promotional plan that will launch on March 1st and will run for 30 days. In order for the promotion to launch I need the following items:

• A landing page, which will take 1 week to code, 2 weeks to design and 1 week to write copy, plus 2 days between each for approvals and revisions.
• An email that is going out on launch day (3/1) that is due to the media company 5 business days before it sends, will require 3 days to code, 5 days to design and 2 days to write copy with 1 day at the end for final approval. Plus an additional email that is going out on 3/25.
• Banner ads that are due to the media company 3 business days before launch, and that require 7 business days in design plus 1 day for review/approval.
• Blog posts that will go out every week for the course of the campaign and that require about 2-3 days to write and 1 day to get proofed.

Plus, our company is closed 2/15-2/18 and no work will be accomplished on those days.

Here is the spreadsheet I created that calculates all the “start/due” dates for each activity. Below I’ll walk step-by-step through how/why I did what. Note: I always set up these type of spreadsheets as templates I can use on multiple projects, so in the spreadsheet I’ve highlight the only cells (in yellow) that should be changed when using for a different project.

1. To start, I’ll enter the closed “holidays” in a separate tab/worksheet. The dates are in cells A1:A4.
2. Then I’ll create a new tab called “Project Plan”. I’ll enter the start date (B2), the promotion length (B3) and then in B4 I’ll add B2 + B3 together to get the promotion end date
3. In row 5 I’ll add the headers for each of my columns. Then I’ll use Freeze Panes to make it so rows 1-5 show up no matter how far down I scroll in the worksheet.
4. In row 7 I added a header for my section “Landing page” by merging 3 cells together and highlighting the cells in gray.
5. Then in Column A I outlined all the tasks in reverse order
6. In Column B I added how long (in business days) each activity should take
7. In C8 I linked to B1, which is the go live date.
8. In C9 I created the =WORKDAY( formula. The logic for this formula is: the first value (C8 in my example) is the “start date”, the second value (B9 in my example) is the number of days we are adding the to the start date, and the third value is the holidays that I entered in step 1 (NOTE: I added dollar signs between the cell references so that I can copy the formula without breaking the holiday calculations). You’ll notice that instead of calculating from the date I’m starting my project, I’m calculating based on the date I want my project to end, so instead of adding days, I want to subtract them. That is why there is a minus sign in front of B9.
9. I then copied this formula to C10 through C14.
10. I repeated steps 4-9 for each of the different deliverables.
11. On the blogs, I simply copied and pasted each section and then added 7 days to the previous “Go Live” date.
12. Then, to make this spreadsheet more useful, I highlight rows 5 through 62 and then turn on the filter. Now, I can create a filter that shows me every activity that needs to be done in a particular period of time. Here’s an example of filtering so that only February dates and blank values show up (I keep the blank values so I can see the section headers).

Let me know if this was helpful. I know that I didn’t explain all the commands necessary to execute this type of spreadsheet, so let me know if additional details or posts will be useful.

Right now I’m planning every promotion we’ll be doing in 2013. This represents hundreds of different promotions that will be managed by more than a dozen people. I’ve been using Excel to build my list of promotions so that I could:

• Easily filter them by categories (huge vs mini promotion, sale vs product promotion, etc.)
• Filter them by date or date ranges
• Filter them by who will be running the promotion
• Identify the start or end date and then calculate the other date based on how long the promotion should last

The information is great, but I was having a hard time wrapping my mind around what was going on and how they overlapped. In these situations I find that visuals are incredibly helpful in identifying how much activity will happen in a certain period of time. What I needed was a Gantt chart without the complexity of Microsoft Project. I did a Google search (which is pretty much how I’ve learned to do everything in Excel) and found this YouTube video that taught me to do exactly what I wanted to do (not to mention the instructor is Irish and I LOVE accents!).

I ended up with a chart that was several hundred lines long, which was pretty but wasn’t particularly useful. As I played with different options, I realized that the chart only displays visible cells. So I could filter the data and get really useful visuals. This means I can filter by an individual and see what projects they own over the course of the year and how they overlap AND I can simply look at specific month and see how many promotions would overlap.

I was so excited that I just had to show some of my co-workers. It looks like we’ll be using this simple chart to give us visuals for several other planning-related activities. #soexcited