Author: Oscar Cronquist Article last updated on March 12, 2019

The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3.

The following array formula in cell C7 extracts weeks that begin on a Sunday:

=IFERROR(SMALL(IF(TEXT($B$3+(ROW($A$1:INDEX($A$1:$A$1000,$C$3-$B$3))-1),"DDD")="Sat",$B$3+(ROW($A$1:INDEX($A$1:$A$1000,$C$3-$B$3))-1),""),ROWS($A$1:A2)),"")

Formula in cell B7:

=IFERROR(C7-6,"")

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Explaining formula in cell C7

Step 1 - Create array 1 to n

The INDEX function creates a cell reference with the same number of rows as there are dates in the date range.

The ROW function then converts the cell range to an array of corresponding row numbers.

ROW($A$1:INDEX($A$1:$A$1000, $C$3-$B$3))

becomes

ROW($A$1:INDEX($A$1:$A$1000, 40633-40569))

becomes

ROW($A$1:INDEX($A$1:$A$1000, 64))

becomes

ROW($A$1:$A$64)

and returns

{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; 31; 32; 33; 34; 35; 36; 37; 38; 39; 40; 41; 42; 43; 44; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63; 64}

Step 2 - Create dates within date range

The next step subtracts the array with 1 and adds the Excel date number.

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

becomes

$B$3+({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; 31; 32; 33; 34; 35; 36; 37; 38; 39; 40; 41; 42; 43; 44; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63; 64}-1)

becomes

$B$3+{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; 30; 31; 32; 33; 34; 35; 36; 37; 38; 39; 40; 41; 42; 43; 44; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63}

becomes

40569+{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; 30; 31; 32; 33; 34; 35; 36; 37; 38; 39; 40; 41; 42; 43; 44; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63}

and returns

{40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603; 40604; 40605; 40606; 40607; 40608; 40609; 40610; 40611; 40612; 40613; 40614; 40615; 40616; 40617; 40618; 40619; 40620; 40621; 40622; 40623; 40624; 40625; 40626; 40627; 40628; 40629; 40630; 40631; 40632}

Step 3 - Convert dates to weekdays

The TEXT function converts the dates to weekdays.

TEXT($B$3+(ROW($A$1:INDEX($A$1:$A$1000, $C$3-$B$3))-1), "DDD")

becomes

TEXT({40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603; 40604; 40605; 40606; 40607; 40608; 40609; 40610; 40611; 40612; 40613; 40614; 40615; 40616; 40617; 40618; 40619; 40620; 40621; 40622; 40623; 40624; 40625; 40626; 40627; 40628; 40629; 40630; 40631; 40632}, "DDD")

and returns

{"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"; "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"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"}

Step 4 - Extract all Sundays

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

IF(TEXT($B$3+(ROW($A$1:INDEX($A$1:$A$1000, $C$3-$B$3))-1), "DDD")="Sat", $B$3+(ROW($A$1:INDEX($A$1:$A$1000, $C$3-$B$3))-1), "")

becomes

IF({"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"; "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"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"; "Mon"; "Tue"; "Wed"}="Sun", $B$3+(ROW($A$1:INDEX($A$1:$A$1000, $C$3-$B$3))-1), "")

becomes

IF({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}, {40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603; 40604; 40605; 40606; 40607; 40608; 40609; 40610; 40611; 40612; 40613; 40614; 40615; 40616; 40617; 40618; 40619; 40620; 40621; 40622; 40623; 40624; 40625; 40626; 40627; 40628; 40629; 40630; 40631; 40632}, "")

and returns

{""; ""; ""; ""; 40573; ""; ""; ""; ""; ""; ""; 40580; ""; ""; ""; ""; ""; ""; 40587; ""; ""; ""; ""; ""; ""; 40594; ""; ""; ""; ""; ""; ""; 40601; ""; ""; ""; ""; ""; ""; 40608; ""; ""; ""; ""; ""; ""; 40615; ""; ""; ""; ""; ""; ""; 40622; ""; ""; ""; ""; ""; ""; 40629; ""; ""; ""}

Step 5 - Find n-th Sunday within range

To be able to return a new value in a cell each I use the SMALL function to filter date numbers from smallest to largest.

SMALL(IF(TEXT($B$3+(ROW($A$1:INDEX($A$1:$A$1000, $C$3-$B$3))-1), "DDD")="Sat", $B$3+(ROW($A$1:INDEX($A$1:$A$1000, $C$3-$B$3))-1), ""),ROWS($A$1:A2))

becomes

SMALL({""; ""; ""; ""; 40573; ""; ""; ""; ""; ""; ""; 40580; ""; ""; ""; ""; ""; ""; 40587; ""; ""; ""; ""; ""; ""; 40594; ""; ""; ""; ""; ""; ""; 40601; ""; ""; ""; ""; ""; ""; 40608; ""; ""; ""; ""; ""; ""; 40615; ""; ""; ""; ""; ""; ""; 40622; ""; ""; ""; ""; ""; ""; 40629; ""; ""; ""}),ROWS($A$1:A2))

becomes

SMALL({""; ""; ""; ""; 40573; ""; ""; ""; ""; ""; ""; 40580; ""; ""; ""; ""; ""; ""; 40587; ""; ""; ""; ""; ""; ""; 40594; ""; ""; ""; ""; ""; ""; 40601; ""; ""; ""; ""; ""; ""; 40608; ""; ""; ""; ""; ""; ""; 40615; ""; ""; ""; ""; ""; ""; 40622; ""; ""; ""; ""; ""; ""; 40629; ""; ""; ""}),2)

and returns 40580 formatted as 2/5/2011.

Step 6 - Return blank if error

The IFERROR function handles errors, it returns a specified value if formula returns an error. In this case it returns a blank "".

Download Excel file


* You will also get a weekly newsletter, unsubscribe anytime!