Formatting Issues

Fix Excel Autofill Changing Year Instead of Day

Maira Mehtab Sep 22, 2026 868 views

Question details

The user is trying to autofill a sequence of dates by dragging a cell downward, but the year increments instead of the day.

Product: Excel
Device & OS: not provided
Scenario: Using the autofill handle to create a sequential list of dates in a spreadsheet column.
Observed behavior: Dragging the date downward changes only the year, and the Fill Series dialog is missing the standard day, month, or year options because Excel is treating the input as text.

Before you start

Check the default alignment of your date in the cell; if it aligns to the left, Excel is reading it as text rather than a recognizable date.

Solution 1Recommended

Convert Text Format to a Recognized Date Format

Re-enter the starting date in a universally recognized format so Excel categorizes the cell as a date rather than plain text.

When Excel does not recognize the format of a date entered into a cell, it defaults to treating the data as text. Text values lack the underlying calendar properties required for the day or month autofill functions to work properly. Fixing the format allows the Fill Series function to recognize the chronological sequence.

Enter a universally recognized date

Select the first cell of your intended series and type the date in a clearly recognizable format, such as 24-Feb-2023, then press Enter.

Apply the desired date format

Right-click the cell, select 'Format Cells', navigate to the 'Number' tab, choose 'Date', and select your preferred layout.

Autofill the series

Click the cell to select it, then click and drag the fill handle (the small square at the bottom-right corner) downward to automatically fill the subsequent days.

Alignment Check: Once properly recognized as a date, the value will automatically align to the right side of the cell by default.

Smart Data Management

Easily Autofill Dates with WPS Spreadsheet

WPS Spreadsheet features intelligent data recognition, automatically identifying date inputs to ensure your series fills correctly without complicated formatting steps.

1. Open your document: Launch WPS Spreadsheet and open the file where you need to input dates.
2. Input the starting date: Type your starting date in the first cell and press Enter.
3. Drag the fill handle: Click the small square at the bottom right of the cell and drag it down your column.
4. Select fill options: Click the small 'AutoFill Options' icon that appears next to your selection and choose 'Fill Days' or 'Fill Months' as needed.

Intelligent date recognition for flawless day, month, or year autofill.Seamless compatibility with Microsoft Excel (.xlsx) files and formatting.Free, lightweight, and features a familiar interface for zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why are the Day, Month, and Year options missing from the Fill Series dialog?

These options only appear when Excel identifies the selected cell's contents as a date. If your cell is formatted as text, the Fill Series dialog will only display standard text or numerical fill options.

How can I fix a whole column of dates currently stored as text?

You can convert them in bulk by selecting the column, going to the 'Data' tab, and clicking 'Text to Columns'. Choose 'Delimited', click 'Next' twice, and on the third screen, select 'Date' and pick the format that matches your current text layout. Click 'Finish'.

Does my computer's region setting affect Excel date recognition?

Yes. Excel relies on your system's regional settings to parse dates. If your system expects MM/DD/YYYY but you type DD/MM/YYYY, Excel will treat it as invalid and format it as text. You can change this in your computer's Control Panel or Settings.

Document & File Issues