Data Import & Export

Fix Excel Dates Shifting by 4 Years After Power Query CSV Import

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to resolve an issue where dates shift by approximately four years (1,462 days) when importing CSV data into an Excel workbook using Power Query.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing CSV data into an Excel workbook using the Power Query tool.
Observed behavior: Dates imported via Power Query shift by 1,462 days (four years and one day) compared to the original date values in the CSV file.

Before you start

Before changing your workbook's date system, verify if it already contains manual date entries, as modifying this setting will immediately shift all existing dates by 4 years and 1 day.

Solution 1Recommended

Standardize to the 1900 Date System Across Workbooks

Ensure all related Excel workbooks use the standard 1900 date system to prevent Power Query from misinterpreting CSV date values.

Excel supports two date systems: the 1900 date system (default for Windows) and the 1904 date system (historically the default for older Macs). Power Query can sometimes conflict with CSV imports if the destination workbook has the 1904 date system enabled, causing exactly a 1,462-day discrepancy.

Open Excel Options

Click on 'File' in the top ribbon and select 'Options' at the bottom of the menu.

Navigate to Advanced Settings

In the Excel Options dialog box, select 'Advanced' from the left-hand navigation pane.

Disable the 1904 Date System

Scroll down to the 'When calculating this workbook' section. Uncheck the box labeled 'Use 1904 date system'.

Apply and Refresh

Click 'OK' to save your changes. Go to the 'Data' tab and click 'Refresh All' to re-run your Power Query import with the corrected date system.

Existing Dates Will Shift: If you already had dates entered in the workbook before changing this setting, they will automatically shift by 1,462 days. You may need to manually correct those specific cells.

Solution 2

Manually Correct Shifted Dates Using Paste Special

If you cannot change the workbook's date system or need to fix dates that have already shifted, you can adjust them mathematically.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Imports

If you frequently experience complex date formatting and import errors in Microsoft Excel, consider switching to WPS Office. It provides a lightweight, highly compatible alternative for handling CSV data and spreadsheets without complicated query errors.

1. Open WPS Spreadsheet: Launch WPS Office and open the Spreadsheet module.
2. Import Your CSV Data: Navigate to the 'Data' tab, click on 'Import Data', and select your target CSV file.
3. Map Data Accurately: Follow the intuitive text import wizard to map delimiters and column formats, ensuring your dates import exactly as intended.

Fully compatible with Microsoft Excel formats (.xlsx, .csv, .xls).Straightforward CSV data import process without unexpected date system shifts.Lightweight installation and fast processing for large datasets.Familiar spreadsheet interface that requires zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my Excel dates shift by exactly 4 years and 1 day?

This happens due to a conflict between the 1900 and 1904 date systems. Excel for Windows typically starts counting dates from January 1, 1900, while older Mac versions start from January 1, 1904. The difference between these two starting points is exactly 1,462 days.

Can I keep the 1904 date system active while using Power Query?

If you must retain the 1904 date system for compatibility with other workbooks, Power Query imports from CSVs might continue to shift. In this case, you will need to create a custom column within Power Query to mathematically add or subtract 1,462 days from the imported date values before loading them into Excel.

Does the 1904 date system affect other data types like text or numbers?

No, the 1904 date system setting only affects numeric values formatted as dates or times. Text, standard numbers, and other data types imported via Power Query will remain completely unchanged.

Excel & Data Problems