Function Problems

How to Create Repeated Dates in Excel Based on Weekly Quantities

Phi Hung Vo Oct 7, 2026 870 views

Question details

Generate a dynamic sequence where specific start dates are repeated automatically based on an adjacent row of numerical quantities.

How to Generate Repeated Dates Based on Specific Quantities in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Transforming weekly summarized data into an expanded row-by-row dataset where each date is duplicated corresponding to its weekly count.
Observed behavior: The user requires a single formula or automated solution that spills the correct number of repeated dates without manual copy-pasting.

Before you start

Ensure you are using a recent version of Excel (such as Microsoft 365) or WPS Spreadsheet that supports dynamic array functions like LAMBDA, REDUCE, and VSTACK.

Solution 1Recommended

Use REDUCE and EXPAND Functions for Dynamic Expansion

This method uses a single-cell dynamic array formula to directly expand the dates according to their corresponding counts without requiring custom named functions.

By utilizing the REDUCE function in combination with EXPAND and VSTACK, you can iterate over your range of dates and stack them dynamically based on the quantity cell located above each date.

Identify Your Data Range

Assume your weekly quantities are located in cells B1:H1, and the corresponding weekly start dates are directly below them in cells B2:H2.

Input the Formula

Select an empty cell where you want the repeated dates to spill down, and enter the following formula: =DROP(REDUCE("",B2:H2,LAMBDA(a,b,VSTACK(a,EXPAND(b,OFFSET(b,-1,0),,b)))),1)

Format as Dates

After pressing Enter, the array will spill down. Select the spilled range, navigate to the Home tab, and change the Number Format to Short Date.

Use REDUCE and EXPAND Functions for Dynamic Expansion

Understanding the OFFSET Function: In this formula, OFFSET(b,-1,0) looks at the cell immediately above the date (which contains the quantity) to determine how many times to expand (repeat) that specific date.

Solution 2

Create a Custom LAMBDA Function in Name Manager

Define a reusable custom function named 'Repeat' to handle complex array expansions seamlessly across your entire workbook.

Advanced Formulas in WPS Office

Easily Handle Dynamic Arrays and Complex Formulas with WPS Spreadsheet

WPS Spreadsheet offers powerful support for modern dynamic array functions, allowing you to manipulate, expand, and transform your data effortlessly without complex macros.

1. Open Your Dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the quantities in the first row and dates in the second row.
2. Enter the Expansion Formula: Click on your desired destination cell and type =DROP(REDUCE("",B2:H2,LAMBDA(a,b,VSTACK(a,EXPAND(b,OFFSET(b,-1,0),,b)))),1).
3. Apply Date Formatting: Press Enter to execute. Highlight the newly generated column, right-click to choose 'Format Cells', and select 'Date' to correctly display the repeated dates.

Fully compatible with Microsoft Excel formats (.xlsx) and formulasRobust support for dynamic array functions like VSTACK, REDUCE, and LAMBDAFree and lightweight spreadsheet solution for advanced data reportingIntuitive interface making data manipulation fast and accessible

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a #NAME? error?

This error occurs if your spreadsheet software version does not support modern dynamic array functions like LAMBDA, REDUCE, or VSTACK. Ensure you are using the latest version of Microsoft 365 or WPS Office.

How do I format the resulting spilled numbers as dates?

Excel calculates dates as serial numbers. To fix this, select the cell containing the formula and the spilled range, press Ctrl+1 to open Format Cells, choose the Date category, and select your preferred format.

What does the DROP function do in this formula?

The REDUCE function often requires a starting value (like an empty string), which gets placed at the top of the generated array. The DROP function is used here to remove that initial empty row, leaving only your generated dates.

Can I adapt this formula for data in columns instead of rows?

Yes. If your data is arranged in columns, you can modify the OFFSET parameter to OFFSET(b,0,-1) to reference the column to the left, and adjust the ranges from B2:H2 to something like B2:B10.

Excel & Data Problems