Excel Practice
Lessons Lesson 83

Working Days: NETWORKDAYS and WORKDAY

About this lesson

NETWORKDAYS counts the Monday-to-Friday days between two dates, both ends included. Any holidays you list are left out. WORKDAY returns the date a given number of working days from a start.

By the end you can

  • count working days between two dates with NETWORKDAYS.
  • find a date a number of working days ahead with WORKDAY.
  • keep holidays in one range and pass them to both.

Practises NETWORKDAYS WORKDAY DAYS WEEKDAY

The idea

Two functions that share one idea and one optional argument. NETWORKDAYS turns two dates into a count of working days. WORKDAY turns a date and a count into the date at the end. Both skip Saturdays and Sundays on their own. Both take a range of holiday dates as their last argument.

The mistake is the calendar living in the formula. Holidays typed into one WORKDAY and forgotten in the next is how two due dates on the same sheet disagree. Keep the days off in one range, lock it, and pass it everywhere. And note that NETWORKDAYS counts both ends. Monday to Friday is 5, not 4.

The mistake to watch for

The calendar living in the formula. Holidays typed into one WORKDAY and forgotten in the next is how two due dates on the same sheet disagree. Keep the days off in one locked range and pass it everywhere. And NETWORKDAYS counts both ends, so Monday to Friday is 5, not 4.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In D2, count the working days from when order 1 was placed to when it shipped, with NETWORKDAYS.

To begin, type it exactly:

=NETWORKDAYS(B2,C2)

D2
Row A B C D E F G
1 Order Placed Shipped Working days Placed shown Shipped shown
2 1 45369 45373 18 Mar 22 Mar
3 2 45376 45384 25 Mar 2 Apr
4 3 45380 45383 29 Mar 1 Apr
5
6 Days off 45383 1 Apr
7 45387 5 Apr
8
9 Lead time 5
10 Order 2 due
11 With days off

Every step

  1. 1

    In D2, count the working days from when order 1 was placed to when it shipped, with NETWORKDAYS. To begin, type it exactly: =NETWORKDAYS(B2,C2).

    Hint. Start date, end date.

  2. 2

    Order 2 spans a weekend. Read =NETWORKDAYS(B3,C3) and say what it returns.

    Hint. Skip the weekend.

  3. 3

    The two dates in B6 and B7 were days off. In D3, count order 2's working days again with those left out. Pass the two cells as NETWORKDAYS' third argument.

    Hint. Third argument: the days-off range.

  4. 4

    A replacement for order 2 is due five working days after it shipped. In B10, find that date with WORKDAY, using the lead time in B9.

    Hint. Start date, then the count.

  5. 5

    In B11, write a formula using WORKDAY that gives the same due date. This time, also skip the days off in B6 and B7.

    Hint. Third argument again.