Function Problems

Normalize Date Columns in Excel with Dynamic Array Formulas

Steven ParkerSteven Parker Nov 18, 2026 869 views

Question details

Convert multiple date columns into rows containing two text identifiers, the date column name, and its corresponding date.

Normalize Date Columns in Excel with Dynamic Array Formulas
Product
Microsoft Excel
Device & OS
not provided
Scenario
A source table has Text 1 and Text 2 identifiers followed by multiple date columns that need to be normalized for analysis.
Observed behavior
The desired output has one row for each source record and date column, regardless of whether the number of source rows matches the number of date columns.
Before you start

Arrange Text 1 and Text 2 in adjacent columns and identify the first and last date columns. For the formula method, use an Excel version that supports REDUCE, LAMBDA, VSTACK, HSTACK, DROP, and CHOOSECOLS.

Solution 1Recommended

Normalize Date Columns with a Dynamic Array Formula

Use this formula to spill one row per record and date column without assuming the row and column counts are equal.

The formula iterates over the date columns and stacks each column's values alongside the identifiers and its header. It uses the actual number of date columns and source rows.

This example assumes a nonempty Excel table named Table1, adjacent identifier columns named Text 1 and Text 2, and a contiguous date range from Date 1 through Date 5.

1
Create the source table

Select the source data and choose Insert > Table. In the Table Design tab, name the table Table1, or substitute your table's name in the formula.

2
Add output headers

In an empty area outside the source table, enter Text 1, Text 2, Date column, and Date in four adjacent cells.

3
Enter the formula

In the cell below the first output header, enter: =LET(ids,Table1[[Text 1]:[Text 2]],dates,Table1[[Date 1]:[Date 5]],headers,Table1[[#Headers],[Date 1]:[Date 5]],DROP(REDUCE("",SEQUENCE(COLUMNS(dates)),LAMBDA(acc,i,VSTACK(acc,HSTACK(ids,IF(SEQUENCE(ROWS(dates))>0,INDEX(headers,1,i),""),CHOOSECOLS(dates,i))))),1))

4
Format and check the results

Select the fourth output column and choose Home > Number Format > Short Date or another date format. Keep the spill area clear, and update the formula's header references if your table or date column names differ.

Normalize Date Columns with a Dynamic Array Formula
Date range and blank values: The formula includes only the date columns between the named endpoints. Change Date 5 in both references if you add date columns beyond that endpoint. Blank source dates may appear as zero in the formula output; Power Query can omit null dates instead.
Free Microsoft Office alternative

Work with Excel Tables in WPS Office

WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Open existing Excel files to make migration easier; Excel-specific formulas and Power Query features may differ.

View and edit spreadsheet tables with a familiar interfaceOpen and save Microsoft Excel XLSX filesWork with date columns and spreadsheet data in WPS SpreadsheetsUse a lightweight office suite with free tools
microsoft office alternative - wps office

Frequently Asked Questions

Does the number of records have to equal the number of date columns?

No. The formula uses ROWS(dates) for the source record count and COLUMNS(dates) for the date column count, so the two counts can differ.

How do I include a new date column in the formula?

If the new column is beyond the current endpoint, replace Date 5 in both the dates and headers references with the new last date column's header.

Why does the formula show a spill error?

The output needs an empty area outside the source table. Clear cells blocking the intended spill range and enter the formula in a regular worksheet cell.

How do I update the Power Query output after editing the source table?

Choose Data > Refresh All in Excel. If you used Unpivot Other Columns, new source columns other than the two identifiers will also be unpivoted.

Why are blank dates missing from the Power Query output?

Unpivoting in Power Query omits null values. Check the source date cells before loading if you need to account for records with missing dates.