When you're trying to determine how many days you have left before reaching a project deadline, try this handy Excel technique.
You don't need to count out days on a calendar to find out how many days you have to complete a project. Excel's NETWORKDAYS function can calculate it for you -- without requiring you to work weekends or holidays.
LEARN MORE: Office 365 Consumer pricing and features
To set up a worksheet that will calculate the number of working days excluding weekends and holidays from a project's start date to its due date, follow these steps:
- Enter the headings for Project, Start Date, End Date, and Work Days as shown.
- Select B2:C2.
- Press Shift+ Ctrl + Down Arrow.
- Right-click the selection and choose Format Cells.
- Select Date under Category and 3/14/01 under Type.
- Click OK.
- Select J2.
- Press Shift+ Ctrl + Down Arrow.
- Right-click the selection and choose Format cells.
- Select Date under Category and 14-Mar-01 under Type.
- Click OK.
- Complete the Holiday Table as shown.
- Select J2:J9.
- Click in the Name box and enter Holidays_2008.
- Click D2 and enter the following formula:
=NETWORKDAYS(B2,C2,Holidays_2008)
- Copy the formula in D2 to the D3:D4 to obtain the number of Workdays for Projects B and C.
Miss an Excel tip?
Check out the Microsoft Excel archive and catch up on other Excel tips.
Also see
- How to add a drop-down list to an Excel cell (TechRepublic)
- A simple Excel timesheet for tracking work hours (free download) (TechRepublic)
- Six clicks: Excel power tips to make you an instant expert (ZDNet)
- A cheatsheet of Excel shortcuts that make inserting data faster (TechRepublic)
- You've been using Excel wrong all along (and that's OK) (ZDNet)
- 10 advanced formatting tricks for Excel users (TechRepublic)
Affiliate disclosure: TechRepublic may earn a commission from the products and services featured on this page.