NETWORKDAYS function returns the number of whole workdays between two dates. The array formula in this blog post counts for example Mondays or any weekday in a date range. Later in this blog post I will show you how to exclude holidays.

Array formula in cell D2:

=SUMPRODUCT(IF(TEXT($B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1), "ddd")=D1, 1, 0))

How to create an array formula

  1. Select cell D2
  2. Paste array formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

How to copy array formula

  1. Copy cell D2
  2. Select cell range E2:J2
  3. Paste

Explaining formula in cell D2

Step 1 - Create dates in the date range

$B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1)

becomes

41000+(ROW($A$1:INDEX($A$1:$A$1000, 41029-(41000-1)))-1)

becomes

41000+(ROW($A$1:INDEX($A$1:$A$1000, 41029-40999))-1)

becomes

41000+(ROW($A$1:INDEX($A$1:$A$1000, 41029-40999))-1)

becomes

41000+(ROW($A$1:INDEX($A$1:$A$1000, 30))-1)

becomes

41000+(ROW($A$1:$A$30)-1)

becomes

41000+({1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30}-1)

becomes

41000+{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29}

and returns

{41000, 41001, 41002, 41003, 41004, 41005, 41006, 41007, 41008, 41009, 41010, 41011, 41012, 41013, 41014, 41015, 41016, 41017, 41018, 41019, 41020, 41021, 41022, 41023, 41024, 41025, 41026, 41027, 41028, 41029}

Step 2 - Convert dates to days of the week

TEXT($B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1), "ddd")

becomes

TEXT({41000, 41001, 41002, 41003, 41004, 41005, 41006, 41007, 41008, 41009, 41010, 41011, 41012, 41013, 41014, 41015, 41016, 41017, 41018, 41019, 41020, 41021, 41022, 41023, 41024, 41025, 41026, 41027, 41028, 41029}, "ddd")

and returns

{"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon"}

Step 3 - Check if values in array are equal to the value in cell D1 (Mon)

IF(TEXT($B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1), "ddd")=D1, 1, 0)

becomes

IF({"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon"}, "ddd")="Mon", 1, 0)

and returns

{0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1}

Step 4 - Sum values in array

SUMPRODUCT(IF(TEXT($B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1), "ddd")=D1, 1, 0))

becomes

SUMPRODUCT({0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1})

and returns 5 in cell D2.

Exclude holidays

Array formula in cell D7:

=SUMPRODUCT(IF(TEXT(IF(COUNTIF($B$10:$B$12, $B$1+(ROW($A$1:INDEX($A$1:$A$1000, ($B$2)-($B$1-1)))-1))=0, $B$1+(ROW($A$1:INDEX($A$1:$A$1000, ($B$2)-($B$1-1)))-1), ""), "ddd")=D6, 1, 0))

How to create an array formula

  1. Select cell D7
  2. Paste array formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

How to copy array formula

  1. Copy cell D7
  2. Select cell range E7:J7
  3. Paste