Building Project Plans in Excel? Watch those Weekends and Holidays!

Many people turn to Excel when the need arises to build a simple project plan. After all, Excel lets you track dates, deliverables and budgets. But if you are planning to calculate start and finish dates in Excel with a formula, you do need to be mindful of working and non-working days.

Here is an example of an Excel project plan that we see all the time. It has a simple list of tasks and their associated dates. In this example, the finish date for each task is calculated by adding the duration to the start date.

End Date = Start Date + Duration

Simple enough, but the dates highlighted in red show us where the problem starts to appear: Adding duration to the start date doesn’t account for weekends. Unless you’re planning to put in some extra hours, you’ll need to tell Excel to use the duration to calculate workdays instead of calendar days. Fortunately, Excel has a handy formula for this:

End Date = WORKDAY(Start Date, Duration)

When we use the workday formula instead, the schedule looks much more reasonable:

Of course, we still haven’t accounted for holidays. The workday formula allows us to do this by adding a list of holidays:

End Date = WORKDAY(Start Date, Duration, Holidays)

In this example, we’ll include New Year’s Day (January 2, 2017) and Martin Luther King, Jr. Day (January 16, 2017) in a holiday table on a separate worksheet in Excel:

Include as many holidays as you need to, since the workday formula can take in a whole list of them. Now, we just modify the formula to consider holidays:

That’s it! You now have a project schedule that is a lot more accurate and you don’t have to worry about scheduling work when people aren’t planning to show up.

This entry was posted in Excel Tips by Safford. Bookmark the permalink.

About Safford

Safford is a versatile technology professional with a solid history of empowering emerging growth companies in a broad array of industries. His employment history includes energy industry consulting at Quorum Software, Senior Manager of Client Services and Technical Sales at telecom service aggregator GetConnected, and Vice President of Strategic Partner Management at electronic payment processor IP Commerce. Prior to his tenure as OnePager's COO, Safford was the company's Vice President of Marketing and Alliances. Safford holds a BA in Psychology and management from Rice University.

Leave a Reply

Your email address will not be published. Required fields are marked *