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

By Ali, Founder, Excel Lessons

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.

Read more about this

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, type:

=NETWORKDAYS(B2,C2)

B2 is when it was placed, C2 when it shipped.

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, type: =NETWORKDAYS(B2,C2). B2 is when it was placed, C2 when it shipped.

    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

    NETWORKDAYS' third argument lists dates to exclude. In D3, count order 2's working days again, with B6:B7 off.

    Hint. Third argument: the days-off range.

  4. 4

    WORKDAY takes a start date, then the number of days. In B10, use WORKDAY on C3 and B9 for order 2's due date.

    Hint. Start date, then the count.

  5. 5

    In B11, add B6:B7 to WORKDAY as the days off. Shape: =WORKDAY(start, n, range).

    Hint. Add it as the third argument, like before.