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.