Calculate the Number of Workdays in a Set Time Period

When you’re planning a project, just looking at a calendar can make you feel like you have a lot more time than you actually have. Particularly at the end of the year, when a number of holidays occur, the period between Thanksgiving and New Year’s can translate into a pretty limited number of workdays.
 
The NETWORKDAYS function in Excel automatically takes into account weekends and (manually assigned) holidays in order to calculate the total number of workdays in a time period. It’s an easy way to figure out if the deadlines you assign have actually allotted enough time to get the work done.
 
Note: This function is included in Excel 2007. To perform it in Excel 2000 and Excel 2003, you’ll first need to install an update. Go to the Tools, menu, click Add-Ins, select the Analysis ToolPak check box, then click OK.
 
First, assign a start date, and end date, and any holidays that fall in between those dates. In the example below, the project begins in mid-November and ends in the second week of the New Year, with time off for Thanksgiving, the day after Thanksgiving, Christmas Day, and New Year’s Day.

 

Go to the Insert menu and select Formula. Go to the Date and Time tab and select NETWORKDAYS.
 
The formula looks like this:
 
=NETWORKDAYS(start_date,end_date,[holidays])
 
 
After you’ve selected the formula, click the cell containing your start date. Then click the cell containing your end date. Finally, click on your first holiday and then drag the mouse down to your last holiday.
 
In the example above, if we’re working in cells B2 through B7, it looks like this:
 
=NETWORKDAYS(B2, B3,B4:B7)
 
Press enter, and you’ll have the net number of working days in the time period. Amazing how nine calendar weeks can translate into just 41 days of work!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: