Function Problems

Create Sequential Weekly Date Ranges with an Excel Formula

Huda QurayshiHuda Qurayshi Nov 18, 2026 870 views

Question details

Generate sequential weekly date ranges and week numbers from a start date and an end date.

Create Sequential Weekly Date Ranges with an Excel Formula
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the dates

Type the first date in A2 and the final allowed date in B2. For example, enter 12/26/2022 and 12/31/2023.

2
Enter the formula

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

3
Review the results

Press Enter and check the ranges in column D and week numbers in column E. Change A2 or B2 to recalculate the list.

Generate Weekly Ranges with a Dynamic Array Formula
Week numbering: WEEKNUM(dates,2) treats the week containing January 1 as week 1. To use ISO week numbers instead, replace WEEKNUM(dates,2) with ISOWEEKNUM(dates).
WPS Spreadsheets

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. 1. Enter your dates: Open WPS Spreadsheets and enter the start date in A2 and the end date in B2.
  2. 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. 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. 4. Add week numbers: Enter =IF(D2="","","Week "&WEEKNUM(D2,2)) in F2 and fill it down alongside the date ranges.
  5. 5. Save the workbook: Use File > Save As and choose the Excel XLSX format if you need to share the workbook with Excel users.
Calculate weekly date ranges and week numbers from editable datesOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet cells and fill-down controlsWork with a free, easy-to-use office suite
microsoft office alternative - wps office

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.