Create Sequential Weekly Date Ranges with an Excel Formula
Question details
Generate sequential weekly date ranges and week numbers from a start date and an end date.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants a spreadsheet to calculate weekly ranges as the date inputs change.
- Observed behavior
- Each output row should show a seven-day date range and its corresponding week number.
Enter valid Excel dates in A2 and B2, with B2 at least six days after A2. Choose a start date that falls on your preferred first day of the week.
Generate Weekly Ranges with a Dynamic Array Formula
Use this formula in an Excel version that supports LET, SEQUENCE, and HSTACK to spill the results into two columns.
The formula includes only complete seven-day ranges ending on or before the date in B2. Week numbers are calculated from each range's start date using Monday as the first day of the week.
Type the first date in A2 and the final allowed date in B2. For example, enter 12/26/2022 and 12/31/2023.
Select D2 and enter =LET(start,A2,finish,B2,n,QUOTIENT(finish-start+1,7),dates,SEQUENCE(n,,start,7),HSTACK(TEXT(dates,"mm/dd/yyyy")&" - "&TEXT(dates+6,"mm/dd/yyyy"),"Week "&WEEKNUM(dates,2))).
Press Enter and check the ranges in column D and week numbers in column E. Change A2 or B2 to recalculate the list.

Create Weekly Ranges with Fill-Down Formulas
Use helper cells if your Excel version does not support HSTACK or dynamic arrays.
Build Weekly Date Ranges in WPS Spreadsheets
Use fill-down formulas in WPS Spreadsheets to calculate complete weekly ranges and week numbers from two date inputs.
- 1. Enter your dates: Open WPS Spreadsheets and enter the start date in A2 and the end date in B2.
- 2. Calculate week starts: Enter =IF($A$2+6<=$B$2,$A$2,"") in D2. Enter =IF(OR(D2="",D2+13>$B$2),"",D2+7) in D3, then drag the fill handle down. Format column D as Date.
- 3. Create the date ranges: Enter =IF(D2="","",TEXT(D2,"mm/dd/yyyy")&" - "&TEXT(D2+6,"mm/dd/yyyy")) in E2, then drag the fill handle down alongside the dates.
- 4. Add week numbers: Enter =IF(D2="","","Week "&WEEKNUM(D2,2)) in F2 and fill it down alongside the date ranges.
- 5. Save the workbook: Use File > Save As and choose the Excel XLSX format if you need to share the workbook with Excel users.

Frequently Asked Questions
Why does the last partial week not appear?
The formulas include only ranges whose seventh day is on or before the end date in B2. Extend B2 if you want another complete range.
How do I use named start and end date cells?
In Excel, select each input cell and assign a name using the Name Box beside the formula bar. Then replace A2 and B2 in the formula with those names.
Why do I see a #SPILL! error?
The dynamic array needs empty cells below and to the right of D2. Clear any content blocking the spill area and enter the formula again.
Can I use ISO week numbers instead?
Yes. Replace WEEKNUM(dates,2) with ISOWEEKNUM(dates) in the dynamic formula, or replace WEEKNUM(D2,2) with ISOWEEKNUM(D2) in the fill-down formula.
Why does Excel not recognize HSTACK?
Your Excel version may not support HSTACK. Use the fill-down formulas instead, or open the workbook in a version of Excel that supports HSTACK.




