Formula Errors

How to Fix Excel Fill Series Date Error for Large Date Ranges

Phi Hung Vo Oct 10, 2026 873 views

Question details

Users need to automatically generate a large sequence of dates using the Fill Series feature but are blocked by a data type error.

How to Fix Excel Fill Series Date Error for Large Date Ranges

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to auto-generate a continuous sequence of dates over a large range using the Home > Fill > Series dialog box.
Observed behavior: Excel throws an error stating that an integer or decimal number is required, failing to recognize the typed end date due to regional or date-format setting conflicts.

Before you start

Verify your computer's regional date settings to ensure Excel recognizes your default date format, or temporarily change your start and end dates into Excel's underlying serial values (e.g., 42005 for January 1, 2015).

Solution 1Recommended

Use Date Serial Values in the Fill Series Dialog

Bypass date format recognition issues by entering Excel's underlying numeric serial values for your starting and ending dates.

Excel stores dates as sequential serial numbers so they can be used in calculations. By using these exact numbers in the Fill Series tool, you eliminate any errors caused by regional text formatting.

Find your serial values

Type your desired start date (e.g., January 1, 2015) and end date (e.g., December 31, 2018) into two empty cells. Select both cells, navigate to the Home tab, and change the Number Format dropdown to 'General' to reveal their serial values (e.g., 42005 and 43465).

Enter the starting serial value

Type your starting serial value (e.g., 42005) into the cell where you want your list to begin, and keep the cell selected.

Open the Series dialog

Navigate to Home > Fill (in the Editing group) > Series.

Configure the series settings

Choose 'Columns' (or 'Rows'), select 'Linear' as the Type, set the Step value to 1, and enter your ending serial value (e.g., 43465) in the Stop value box. Click OK.

Format back to dates

Select the newly generated column of numbers, go to the Home tab, and change the Number Format from 'General' to 'Short Date' or your preferred date format.

Use Date Serial Values in the Fill Series Dialog

Tip: Once the serial values are successfully generated and converted to dates, you can apply any custom date formatting without affecting the underlying data.

Solution 2

Generate the Range Using the SEQUENCE Function

Use Excel's dynamic array SEQUENCE function to instantly output a large date range without using the Fill dialog.

Seamless Data Generation in WPS Office

Easily Generate Date Series with WPS Spreadsheet

WPS Spreadsheet provides a highly compatible, user-friendly interface that efficiently handles date generation, automatically recognizing most regional formats to prevent tedious errors.

1. Enter your starting date: Open your document in WPS Spreadsheet and type your starting date directly into the desired cell.
2. Access the Fill tool: Select the cell, navigate to the Home tab, click the Fill icon in the toolbar, and select 'Series'.
3. Generate the dates: Select 'Columns' or 'Rows', choose 'Date' under Type, input your exact ending date in the Stop value box, and click OK to populate the range.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls) and functions.Intuitive Fill Series tool that seamlessly recognizes standard date formats out of the box.Supports advanced dynamic array functions like SEQUENCE for complex data generation.Free and lightweight alternative for everyday office productivity.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel say 'an integer or decimal number is required' for dates?

Excel stores dates internally as serial numbers. If the date format you type into the Fill Series dialog does not match your computer's system regional settings, Excel fails to recognize it as a number, treats it as text, and consequently displays this error.

How do I find the serial number for a specific date in Excel?

Simply enter your target date into any cell, then change the cell's formatting from 'Date' to 'General' or 'Number' via the Home tab dropdown. The displayed integer is the underlying serial value of that date.

Can I generate a date series across rows instead of columns using SEQUENCE?

Yes. To generate a sequence across a row, you place the calculation in the columns argument of the SEQUENCE function instead of the rows argument. For example, use =SEQUENCE(1, End_Serial - Start_Serial + 1, Start_Serial).

Excel & Data Problems