Calculation Issues

How to Fix Excel Autofill Changing Years Instead of Dates

Bushra Parveen Oct 9, 2026 869 views

Question details

The user is attempting to autofill a sequence of dates, but the software increments the year instead of continuing the day sequence.

How to Fix Excel Autofill Changing Years Instead of Dates

Product: Microsoft Excel
Device & OS: not provided
Scenario: Using the fill handle to drag and generate a consecutive sequence of dates in a worksheet.
Observed behavior: The application increases the year (e.g., from 2023 to 2024 to 2025) instead of incrementing the day. This occurs because the dates are stored as text or use unrecognized regional language formats.

Before you start

Ensure your date cells do not contain hidden leading spaces or apostrophes, as these instantly force the application to treat numeric dates as text strings.

Solution 1Recommended

Convert Text to Valid Dates and Apply Correct Regional Formats

Excel will increment the last number in a text string (the year) if it doesn't recognize the cell as a valid date. Converting the text to a numeric date resolves this.

When Excel encounters a date format it doesn't recognize natively, it treats the entry as plain text. When you drag text that ends in a number, Excel's default behavior is to increment that final number. By converting the column back into proper numeric dates, the autofill feature will recognize it and increment the days correctly.

Select the problematic cells

Highlight the column or range of cells containing the dates that are acting like text.

Use Text to Columns

Navigate to the 'Data' tab on the ribbon and click on 'Text to Columns'. Click 'Next' twice to reach step 3 of the conversion wizard.

Format as Date

Under 'Column data format', select the 'Date' radio button. Choose the correct layout matching your data (e.g., DMY for Day-Month-Year), and click 'Finish'.

Set regional format

Right-click the highlighted cells, select 'Format Cells', go to the 'Number' tab, choose 'Date', and pick the correct locale/region.

Autofill the sequence

Click and drag the fill handle (the small square at the bottom right of the selection) downward to autofill the sequence accurately by days.

Convert Text to Valid Dates and Apply Correct Regional Formats

Quick Verification: Valid numeric dates align to the right side of the cell by default, whereas text aligns to the left.

Solution 2

Use Numeric Dates with Custom Weekday Formatting

If you are typing weekday names in a language Excel doesn't recognize natively, use standard dates and apply a custom format code instead.

WPS Spreadsheet Date Formatting

Easily Manage and Autofill Dates with WPS Office

WPS Spreadsheet offers highly intuitive date recognition and powerful formatting tools, ensuring your autofill sequences work perfectly every time without the frustration of text-formatting errors.

1. Open your file in WPS: Launch WPS Spreadsheet and open the document containing your date sequences.
2. Verify date formatting: Select the cells with date text, right-click, and choose 'Format Cells' to confirm the correct Date locale is applied.
3. Convert text if necessary: If the dates are stuck as text, use the 'Data' tab > 'Text to Columns' feature to force the data into valid numeric dates instantly.
4. Drag to autofill: Click and drag the small fill handle at the bottom-right of the cell to correctly autofill consecutive days.

Highly compatible with Microsoft Excel (.xlsx) files and advanced date formats.Smart text-to-date conversion tools to quickly resolve spreadsheet formatting errors.Supports diverse regional settings and custom date codes natively.Free and lightweight alternative for seamless daily spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel add 1 to the year when I drag a date?

If Excel doesn't recognize your entry as a valid date, it treats it as a text string ending in a number. The default autofill behavior for text is to increment the last number found in the string, which happens to be the year in your format.

How do I force Excel to increment days instead of years or months?

Right-click and drag the fill handle instead of using the standard left-click drag. When you release the mouse button, a context menu will appear where you can explicitly select 'Fill Days', 'Fill Weekdays', 'Fill Months', or 'Fill Years'.

Can I use my native language for weekday names in dates?

Yes, but to ensure autofill works, enter the date numerically first. Then, apply a Custom cell format using a locale code (like [$-0809] for UK English or your specific language code) combined with 'dddd'. This displays the weekday name in your language without converting the cell to text.

Excel & Data Problems