Extract week ranges based on a given date range

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

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 "".

Get the Excel file

Extract-week-ranges-based-on-a-given-date-range-1.xlsx

Dates category

Create a date range [Formula]
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

Find the most recent date that meets a particular condition
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

Formula for matching a date within a date range
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

See all articles in 'dates' category

Functions in this article

IFERROR SMALL IF TEXT ROW ROWS

More than 1300 Excel formulas

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

2 Responses to “Extract week ranges based on a given date range”

aMareis says:

May 13, 2013 at 3:28 am

Hi, Oscar
If B2=1-29-2011 then, your formula is not work.
Here's an alternative. It's not an array formula and shorter.

cell C2:
=IF($A2-WEEKDAY($A2,2)+7*COLUMN(A1)-$B2>=7,"", TEXT(MAX($A2,$A2-WEEKDAY($A2,2)+7*(COLUMN(A1)-1)+1), "m/d/yyyy") &" - "&TEXT(MIN($B2, $A2-WEEKDAY($A2,2)+7*COLUMN(A1)),"m/d/yyyy"))

Reply
- Oscar says:
  
  May 14, 2013 at 2:13 pm
  
  aMareis,
  
  I can´t find the file but thank you for commenting!

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form