Wednesday, June 5, 2013

Calculating Workdays

Do you ever find yourself Counting Workdays until your next vacation, holiday, or other event? If you are like my sister who is retiring in a few weeks, the answer may be a resounding “Yes!”

If you have a set of dates in Excel and you want to calculate the number of Business Days, (excluding weekends and holidays), you can easily do this using the NETWORKDAYS function. The syntax is as follows:

=NETWORKDAYS(StartDate, EndDate, ListOfHolidays)

If you are working with an older version of Excel (prior to Excel 2007), you should note that the NETWORKDAYS was available only after you installed the Analysis ToolPak add-in.

Also, starting with Excel version 2010, if you wish to calculate the workdays using something other than the standard weekend days of Saturday and Sunday, you can use the new NETWORKDAYS.INTL. With this new function, you can customize your weekend days as simply as your holidays.

But let’s suppose you have a start date for Project and you know how many workdays it will (theoretically, at least) take to complete it. How do you calculate the Date that it will be done? Here is where the WORKDAY function comes into play. Using the following syntax, you can easily calculate When the project will be complete:

WORKDAY(StartDate, #OfDays, ListOfHolidays)

If you wish to ascertain that the “StartDate” is interpreted correctly by Excel, (this is a recommended Best Practice), you can use the DATE function in its place, and alleviate any concern in this regard.

Calculating Workdays: As with most anything we encounter, when you use the right tools, it’s like being on vacation…