Extract week ranges based on a given date range
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:
Formula in cell B7:
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 "".
Dates category
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 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
Excel categories
2 Responses to “Extract week ranges based on a given date range”
Leave a Reply
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.
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"))
aMareis,
I can´t find the file but thank you for commenting!