Calculation Issues

How to Fix Excel Autofill for Monthly Dates in UK Date Format

Nimra MalikNimra Malik Oct 23, 2026 868 views

Question details

The user needs to correctly autofill a monthly date series in Excel while using the UK date format in Windows.

How to Fix Excel Autofill for Monthly Dates in UK Date Format
Product
Excel
Device & OS
Windows
Scenario
Attempting to drag and continue a monthly date series using the fill handle.
Observed behavior
Excel fails to continue the monthly date series under the UK date format, whereas it works perfectly with the US format.
Before you start

Ensure that your Windows regional settings are currently set to the UK date format (DD/MM/YYYY) and that your Excel columns are wide enough to display the full date text.

Solution 1Recommended

Use Custom Date Formatting and Pattern Selection

By providing Excel with a clear two-month pattern and applying a custom date format, the autofill handle will correctly recognize and generate the sequential monthly series.

Excel relies on pattern recognition to determine how to increment dragged cells. When dealing with regional format conflicts, establishing the pattern manually using two consecutive dates guarantees the software increments the months instead of the days.

1
Enter the initial dates

Type '1-Jan-2021' in the first cell, and then type '1-Feb-2021' in the cell directly below it to establish a monthly pattern.

2
Select the cells

Click and drag to highlight both cells containing your newly entered dates.

3
Apply custom formatting

Right-click the selected cells, choose 'Format Cells', navigate to the 'Custom' category under the Number tab, and enter `mmm 'yy` in the Type field. Click OK.

4
Drag the fill handle

Click and hold the small green square at the bottom-right corner of your selection, then drag it downward to autofill the subsequent months in the series.

Use Custom Date Formatting and Pattern Selection
Pattern Recognized: Entering two consecutive dates ensures Excel accurately detects the monthly step value, circumventing the regional date format conflict.
Seamless Date Management

Easily Autofill Dates with WPS Spreadsheet

WPS Office Spreadsheet provides intuitive and highly compatible data filling features. You can seamlessly autofill sequential dates, months, and custom formats without worrying about regional Windows conflicts.

  1. 1. Open your spreadsheet: Launch WPS Office and open your existing Excel workbook or create a new blank Spreadsheet.
  2. 2. Input the starting dates: Enter your first two consecutive months (e.g., 01/01/2021 and 01/02/2021) into a column to establish the monthly pattern.
  3. 3. Use the Fill Handle: Highlight both cells, click the fill handle at the bottom right corner of the selection, and drag it downward to automatically generate the monthly series.
Intelligent autofill recognizes complex date patterns effortlessly.Fully compatible with Microsoft Excel (.xlsx, .xls) file formats.Customizable cell formatting matches your exact regional preferences.Free and lightweight alternative for everyday spreadsheet tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel autofill days instead of months?

If you only select a single cell with a date and drag it, Excel defaults to incrementing the series by one day. You must either select two cells to establish a monthly pattern or click the 'AutoFill Options' icon that appears after dragging and choose 'Fill Months'.

How do I change my default date format in Excel to UK format?

Excel pulls its default date format from your Windows Regional Settings. To change it globally, open the Windows Control Panel, go to 'Clock and Region', click 'Region', and set your format to 'English (United Kingdom)'.

Can I use the 'Fill Series' menu to autofill monthly dates?

Yes. Enter your starting date, select the cell, go to the 'Home' tab, and click 'Fill' > 'Series'. Choose 'Columns' or 'Rows', set the Type to 'Date', the Date unit to 'Month', enter a Stop value, and click OK.

Will changing to UK date format affect my existing spreadsheets?

Cells formatted using the system's default 'Short Date' or 'Long Date' options will automatically update to display the UK layout. However, cells with hard-coded custom date text or custom formatting codes will remain unaffected.