'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Day and Month Reversed When Converting PDF or Word to Excel
Question details
The user needs to prevent the day and month from swapping places when date values are imported or converted from PDF and Word documents into Excel.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing or converting tabular data containing dates from a PDF or Word document into a spreadsheet format.
- Observed behavior
- The spreadsheet application automatically interprets the imported date strings based on its default regional settings, causing the day and month values to be reversed.
Verify your computer's system default regional date format (e.g., MM/DD/YYYY vs. DD/MM/YYYY) and compare it to the date format used in the original PDF or Word document.
Import Converted Data as Text
Bypass automatic date conversion by importing the data as plain text, allowing you to manually apply the correct regional date format afterward.
Spreadsheet software often attempts to automatically parse strings that look like dates during standard data imports. If the source document's date format differs from your system's regional settings, the application may inadvertently swap the day and month. Importing the data directly as text prevents this automatic conversion.
Open a blank workbook, navigate to the 'Data' tab, and select 'From Text/CSV' (or the legacy 'From Text' wizard).
Locate and select the intermediate CSV or text file that was generated from your PDF or Word document.
In the import wizard, proceed to the column formatting step, highlight the column containing your dates, and change the format setting from 'General' to 'Text'.
Click 'Finish' or 'Load' to bring the data into your spreadsheet without altering the original day and month layout.
Adjust System Regional Settings Before Conversion
Temporarily change your computer's regional settings to match the source document's date format to ensure Excel interprets the dates correctly.
Use WPS Office for Accurate PDF to Excel Conversions
WPS Office includes an intelligent, built-in PDF to Excel converter that accurately preserves tabular data, dates, and numeric formatting, helping you avoid frustrating issues like reversed days and months.
- 1. Open PDF in WPS Office: Launch WPS Office and open the PDF document containing the data you wish to convert.
- 2. Select PDF to Excel: Navigate to the 'Tools' tab on the top ribbon and click on 'PDF to Excel'.
- 3. Configure Output: Choose your desired page range and layout settings in the conversion dialog box.
- 4. Convert and Verify: Click 'Convert' to generate the spreadsheet. WPS Spreadsheet will automatically open the new file with your dates and formatting preserved.
Frequently Asked Questions
Why does Excel automatically swap the day and month when opening a CSV or converted file?
Excel relies on your computer's system regional settings to interpret date values. If a file contains a date formatted as DD/MM/YYYY but your system is set to MM/DD/YYYY, Excel will try to read the first number as the month, effectively reversing the date.
How can I fix reversed dates in Excel after they have already been imported?
You can fix this using the 'Text to Columns' tool. Select the affected date column, go to the Data tab, and click 'Text to Columns'. Proceed to Step 3 of the wizard, select 'Date', and choose the format that matches how the dates are currently (incorrectly) displayed (e.g., MDY or DMY), then click Finish.
Can I stop WPS Spreadsheet from automatically formatting imported dates?
Yes. When importing external data or CSV files into WPS Spreadsheet, use the 'Import Data' wizard and ensure you highlight the date columns and set their data format to 'Text' before finalizing the import.
