Project Practitioners > Automated Project Scheduling in Excel

Automated Project Scheduling in Excel

By DeAnna Burghart

Have you ever wished you could see a simple project's tasks laid out on a calendar without wrestling with MS Project or similar scheduling software? Maybe you don't have scheduling software installed. Maybe it seems like way too much effort for this project. Maybe you want to be sure the rest of the team can read the schedule as well as you can. Maybe you just like using spreadsheets.  

This week I thought I'd share one of my Frankensheets, in the hope some fellow spreadsheet junkies will find it useful. If complex formulas and conditional formatting make you break out in hives, stop reading now. If, on the other hand, they tend to interfere with date night and give you the same thrill as opening a puzzle box for the first time, you're going to love this.

Give Me Dates, and Don't Make Me Think

The project in question is our recurring webinars and Fast Ramp series. For me, this was a perfect case of a fairly repetitive task list that it didn't make sense to plan in complicated software. There are many moving parts, but they're the same each time, and there's a definite rhythm that benefits from a list structure. I found it easier to simply plug in a known due date and have the software tell me when everything needed to be done. This was possible mainly because I knew certain tasks had to be completed at least X days before the event date, but preferably Y days before in order to leave us plenty of time. So I started by creating a simple list of the necessary tasks, task owners and so on. Once that was done, I determined when a task had to be performed. (4 weeks before the event = 28 days, and so on.) I established some stretch dates along with that. My event date was taken from a single cell at the top of the owner column, to keep all changes in the same place. To keep things really simple, I named that cell "webdate1" so I can use the name in formulas. Using some very simple math, I had a list of stretch dates (sum(webdate1,D5)) and final dates (sum(webdate1,C5)) for my task list. (Hide the min est and max est columns to make it look nice and clean.)

  Planning-excel-weekend-dates

 

That was pretty simple, but the initial list, as you can see, had some quirks. First issue: blank stretch dates left the same due date as the actual event, which is no good. That's easily fixed with an ISBLANK condition, so the stretch date changed to incorporate that:

IF(ISBLANK(D5),"",SUM(webdate1,D5))

The second issue was that some due dates inevitably showed up on weekends. Now, I love my job and all, but even I have limits. Plus, most people prefer to get notice emails when they're working. Fortunately, Excel can calculate the day of week of a given date. All I had to do was determine whether the sum of the event date and the offset date landed on a Sunday (1) or a Saturday (7) and if so, move it to the nearest weekday by adding or subtracting a day as needed. The resulting formula looks much worse than it actually is.  

=IF(WEEKDAY(SUM(webdate1,C5))=1,SUM(webdate1,C5,1),(IF(WEEKDAY(SUM(webdate1,C5))=7,SUM(webdate1,C5,-1),SUM(webdate1,C5))))

Here's a nested version to make it more understandable:

=IF(WEEKDAY(SUM(webdate1,C5))=1, // Is this a Sunday?

SUM(webdate1,C5,1),          // OK, move it to Monday. Done.

(IF(WEEKDAY(SUM(webdate1,C5))=7, // Is it Saturday?

SUM(webdate1,C5,-1),       // OK, move it to Friday. Done.

SUM(webdate1,C5)     // Not Sat or Sun, simple date

)

)

)

 

Like I said, looks worse than it is.* (Don't get me started on my desire for an indent-capable version of the Excel formula window, but of course most people don't build monstrosities like this.)

OK, so now I have a lovely weekdays-only list of all my expected due dates for an event. It looks grand, doesn't it? But it's a bit busy, even with the C and D columns hidden. It's just too hard to see where you are today. Enter conditional formatting.

Conditional Formatting: Make It Look Pretty

Conditional formatting is one of Excel's most powerful features for making data easily scannable. If you routinely use RYG reports or similar color-coded alerts in status and portfolio management, conditional formatting could save you hours of time, and possibly avoid a missed alert. It's well worth understanding.

I find it easier to set up coherent conditions if I map them out before starting. So I decided what I really wanted to know at a glance, and how I would like to have it portrayed.

  • What's our next immediate task? Let's call this anything due in the next 3 days, and bold it in the task list. Stretch dates coming up soon?? Yeah, let's bold items in the task list too.
  • Is that task late? Make that due date pop by changing the cell to a red fill. If "late" date is just one of the stretch dates, orange text will suffice. It doesn't need to hit me in the head. For tasks that are only a little late – let's say, within 3 days – we'll highlight with a yellow background.
  • Is the task done already? Let's make it recede into the background with grey text in the task list. We'll store the task status in column G.  
  • Did our completed task beat the due date? If so, show that date in green. Show missed due dates in red. We'll store the actual completed dates in column H, to preserve the history. (See what I did there?) We'll apply similar logic to our stretch dates (but they don't need to be red, because they're not as important).
  • What if the task is started but not done yet? Well, we'll mark that WIP and leave it grey. If it isn't truly done, though, no credit in our due date columns.

 

When I put it in a quick table, here's how it shakes out:

 Column affected

 Condition

Formatting

Formula

 E (task list)

 Due in next 3 days AND not done
 OR
 Stretch date in the next 3 days AND not done

Bold

 =AND(A5<=(TODAY()-3),G5<>"DONE")

 =AND(B5<=(TODAY()-3),G5<>"DONE")

 A (real date)

 > 3 days late

Red fill

 =AND(G5<>"DONE",(A5<(TODAY()-3)))

 B (stretch dates)

 > 3 days late

Orange text

 =AND(G5<>"DONE",(B5<(TODAY()-3)))

 A (real date)

 Late (< today)

Yellow fill

 =AND(G5<>"DONE",A5<(TODAY())

 E (task list)

 Completed or WIP

Grey text

 =OR(G5="DONE",G5="WIP")

 A (real date)

 <= completed date

Green text

 =AND(G5="DONE",H5<=A5)

 A (real date)

 > completed date

Red text

 =AND(G5="DONE",H5>A5)

 B (stretch dates)

 <= completed date

Green text

 =AND(G5="DONE",H5<=B5)

 B (stretch dates)

 > completed date

Orange text

 =AND(G5="DONE",H5>B5)

The actual creation of the conditional formatting is left as an exercise for the reader, but it's pretty simple. The trick is to highlight the entire area you want to apply the formatting to, but pretend you're only formatting one cell. (That's why the formulas above only refer to row 5.) Excel semi-intelligently applies those formulas to the other cells in the highlighted range.** Here's an example of what the final conditional formatting box looks like for our real due dates.

Planning-excel-conditional-formatting

With this formatting applied, your dates and task lists now change dynamically to reflect your current task status, due dates, and next tasks.

Planning-excel-formatted-list

By changing your ultimate due date (in red above the owner column) you immediately re-populate the task list with a new set of due dates, preformatted to account for your expected project timeline. It's hardly rocket science, but it's a great way to easily track and "plan" repetitive, highly predictable tasks.

But Wait, There's More!

But what if you want to see this on a calendar? You need to know if any of your due dates overlap vacations, critical dates for other projects, routine operational cycles, and so on. That's where a proper calendar comes in. It's a (relatively) simple matter of capturing your first possible due date, using that to create a calendar grid, and using conditional formatting to alert you to possible conflicts. I'll explain in my next post.  

---------------------------------------------------------------------------------------------------

*Note to the truly Excel-savvy: You could probably do this much less messily with a custom formula, but I haven't taken the time. Would be easiest to test the resulting date and add or subtract as necessary. Maybe I'll do that after finishing this post.

**If you prefer, you can start with one cell, then expand the affected range in the "applies to" box. You can probably make this easier by naming your formatted cell ranges, but Excel automatically expands to the ugly cell definitions so that's purely for simplicity of data entry. The box itself won't be any more readable.



Comments
Not all comments are posted. Posted comments are subject to editing for clarity and length.

A useful Excel workbook function to look into is NETWORKDAYS(start_date,end_date,optional_holiday_list) .


Good one, Richard. In this series, I'm generally sticking to functions that run completely on the base platform -- no add-ins or custom functions. But if you have the Analysis Toolpak installed, it's a very elegant solution.


Thanks for this DeAnna, my mgt wants simpler schedules in all our project reviews. I was doing in Excel but was searching for something in PowerPoint now. Any ideas on how to make this look good in PPT? I did find this tool which works with Excel, office timeline. Are you familiar with it?


Post a comment




(Not displayed with comment.)









©Copyright 2000-2017 Emprend, Inc. All Rights Reserved.
About us   Site Map   View current sponsorship opportunities (PDF)
Contact us for more information or e-mail info@projectconnections.com
Terms of Service and Privacy Policy



Stay Connected
Get our latest content delivered to your inbox, every other week. New case studies, articles, templates, online courses, and more. Check out our Newsletter Archive for past issues. Sign Up Now

Follow Us!
Linked In Facebook Twitter RSS Feeds


Got a Question?
Drop us an email or call us toll free:
888-722-5235
7am-5pm Pacific
Monday - Friday
We'd love to talk to you.

Learn more about ProjectConnections and who writes our content. Want to learn more? Compare our membership levels.