How to determine the number of remaining workdays for Microsoft Excel projects

Time management is a huge part of keeping a project on track, so most of us have some kind of software tracking our projects. Although Microsoft Excel isn’t a time management tool, you can use it to keep up with project deadlines. If you’re like most of us, you want to know how many days remain to complete a project on time, and Excel can help.

In this tutorial, I’ll show you how to use Excel’s NETWORKDAYS() function to determine the number of working days remaining for current projects. Notice that I specified working days and not just days. Working days, in the context of this article, are Monday through Friday. In other words, we don’t want the number of remaining days to include any Saturday or Sunday that falls between today and the estimated completion date.

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions of Excel down to Excel 2007. Excel for the web supports NETWORKDAYS().

How to subtract dates in Excel

You might know that you can subtract dates to determine the number of days between two dates. For instance, the simple expression:

=[@Completion]-TODAY()

shown in Figure A, returns the total number of days remaining between the project’s estimated completion date when today is Aug. 7.

Figure A

The reference [@Completion] refers to the corresponding dates in the Table’s Completion column. Excel uses structured reference because the data is in a Table object. If you’re working with a normal data range, you’d use the following expression instead:

=$C5-TODAY()

SEE: Windows, Linux and Mac commands everyone needs to know (free PDF) (TechRepublic)

If you’re not familiar with simple date arithmetic expression, it evaluates as follows:

$C5-TODAY()

8/31/2022-8/7/2022

44804-44780

24

Keep in mind that the TODAY() function updates, so the results you see won’t match the figures in this article. In fact, they may show as overdue, as project 4 does. There’s no way for Excel to know whether you’ve completed the project as is. Because of this, the demonstration file isn’t as useful as you might like, so feel free to change the completion dates to reflect your usage.

There are 24 days remaining between today, Aug. 7, 2022 and Aug. 31, 2022. This simple expression is helpful, but it includes all days between the two dates. If you’re not aware of that, you might make the mistake of thinking you have 24 days left to complete your project without realizing that you’re condemning yourself to also work weekends. Instead, we want a count of only working days to avoid such a commitment.

How to use NETWORKDAYS() in Excel

Thanks to Excel’s NETWORKDAYS(), counting only the working days between two dates is easy. This function evaluates the seven days of the week as follows:

• Monday through Friday are workdays.
• Saturday and Sunday are weekends and excluded from the count.

If your workdays are different from these, use Excel’s NETWORKDAYS.INTL() function, but we’re not going to include that function in this discussion.

NETWORKDAYS() uses the following syntax:

NETWORKDAYS(start_date, end_date, [holidays])

where start_date references the date you start a project and end_date references the date you expect to complete the project. Because we’re calculating the remaining days, start_date will always be the current day, which we’ll achieve by using the TODAY() function. The optional [holiday] argument references a list of holidays to exclude from the count. We’ll discuss that argument a bit later.

You could reference or enter two dates, but we’re not counting the number of days between a start and end date. Instead, we’re counting the number of days between today and the projected completion date. We’ll use, instead, the function:

=NETWORKDAYS(TODAY(),[@Completion])

which evaluates the same as the simple expression we used earlier, but the function won’t include Saturdays and Sundays in the results, as shown in Figure B. There are 24 days between Aug. 7 and Aug. 31, but there are only 18 working days. Tomorrow, when you open the file on Aug. 8, there will be 23 total days remaining and 17 working days.

Figure B

In the U.S., a holiday, Labor Day, falls on Sept. 5 in 2022. If you want to exclude that day from the count, you can use the optional holiday argument.

How to exclude holidays in Excel

So far, we’ve used NETWORKDAYS() to exclude weekends when counting the number of days between today and the estimated completion date for our projects. We have a holiday, Labor Day, to consider for project 3. Fortunately, NETWORKDAYS() can also exclude holidays from the count, but you must specify those holidays.

Figure C shows a simple Table object that includes only one date, Sept. 5, 2022, which is Labor Day in the U.S. In a real working situation, your holiday Table will be much larger, but to keep things simple, ours includes only one.

Figure C

After creating the Table, you can name the date column Holidays or reference it as C3 in the function. To name the cell, do the following:

1. Select C3.
2. Click the Formulas tab.
3. In the Defined Names group, click Define Name.
4. In the resulting dialog, name the cell Holidays (Figure D).
5. Click OK.

Figure D

Notice the Refers To setting, =TableHolidays[Date]. This named range is in a Table named TableHolidays. As you add holidays, the Table will automatically update, so you won’t have to change this reference in the NETWORKDAYS() function.

As you can see in Figure E, the number of remaining days for project 3 is one less than before. Because of the Labor Day holiday you have 25 working days from Aug. 7 to complete the project on time.

Figure E

Project 4 is either overdue or completed. When applying NETWORKDAYS() to your work, you’ll probably include an actual completion date to avoid misleading information. As is, a project could be complete, but the demonstration sheet doesn’t reflect that.