How to Fix Excel Autofill for Monthly Dates in UK Date Format
Question details
The user needs to correctly autofill a monthly date series in Excel while using the UK date format in Windows.

- 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.
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.
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.
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.
Click and drag to highlight both cells containing your newly entered dates.
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.
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.

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. Open your spreadsheet: Launch WPS Office and open your existing Excel workbook or create a new blank Spreadsheet.
- 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. 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.

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.




