'%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 Excel Importing Dates Incorrectly from PDF
Question details
The user needs to correct multiple incorrectly interpreted dates when importing data from a PDF into Excel.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing table data containing dates from a PDF document into a spreadsheet.
- Observed behavior
- Excel incorrectly converts the day and year format during import, for example interpreting May 14, 2024, as 2014-05-01.
Verify the original date format in your source PDF file (e.g., MM/DD/YYYY or DD/MM/YYYY) so you know exactly which regional format to apply during the import process.
Adjust Regional Settings in Power Query
Use Power Query's locale settings to correctly interpret the date formats when importing data from a PDF.
When importing data from a PDF or CSV file, Excel relies on your system's default regional settings to interpret dates. If the source document uses a different date format, days and years might be flipped or misinterpreted. Adjusting the locale inside the Power Query Editor resolves this mismatch.
Go to the Data tab on the Excel ribbon, click on 'Get Data', select 'From File', and choose 'From PDF'. Locate and select your file.
In the Navigator window, select the table containing your dates and click 'Transform Data' to open the Power Query Editor.
Right-click the header of the column with the incorrect dates, go to 'Change Type', and select 'Using Locale' from the context menu.
In the Change Type with Locale dialog box, choose 'Date' as the Data Type. Then, select the Locale (regional format) that matches the date format originally used in your PDF (e.g., English (United States) for MM/DD/YYYY). Click OK.
Click 'Close & Load' in the Home tab to import the correctly formatted dates into your Excel worksheet.
Easily Import and Convert PDF Data with WPS Office
WPS Office offers a dedicated PDF to Excel conversion feature that accurately recognizes table structures and date formats, saving you the hassle of manual data transformation and locale adjustments.
- 1. Open PDF in WPS Office: Launch WPS Office and open the PDF document containing the date tables.
- 2. Select PDF to Excel: Navigate to the 'Tools' tab on the top ribbon and select the 'PDF to Excel' conversion tool.
- 3. Convert and Save: Choose your preferred output settings, click 'Convert', and open your newly created spreadsheet with perfectly formatted dates.
Frequently Asked Questions
Why does Excel swap the days and months when importing data?
Excel interprets imported dates based on your computer's default system regional settings. If the source file uses a US date format (MM/DD/YYYY) but your system is set to a European format (DD/MM/YYYY), Excel may swap the day and month or fail to recognize the date altogether.
How do I fix dates that have already been imported incorrectly?
If dates are already imported as text or jumbled values, you can use the 'Text to Columns' feature in the Data tab. Highlight the affected column, click 'Text to Columns', click Next twice, and on Step 3, choose 'Date' and select the original format of the data (e.g., MDY) before clicking Finish.
Can I change my default date format globally in Excel?
Excel's default date format is controlled by your operating system's regional settings. You can change this in the Windows Control Panel by navigating to 'Clock and Region' > 'Change date, time, or number formats', which will apply to all future Excel workbooks.
