'%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 Incorrect Excel Date Formatting for mm/dd/yy Bank Files
Question details
The user needs to import bank files into Excel and correctly parse mm/dd/yy formatted dates without altering the computer's overall Windows regional settings.
- Product
- Microsoft Excel
- Device & OS
- Windows
- Scenario
- Importing exported bank data (like CSV or text files) containing dates in the mm/dd/yy format.
- Observed behavior
- Excel automatically misinterprets the dates by applying the default system regional settings, swapping months and days or breaking the date format entirely.
Ensure you have the original, unedited bank export file (typically a CSV or TXT file) saved in an accessible folder on your computer.
Use Power Query to Specify the Correct Date Format
This method allows you to define the exact date format and locale during the import process, preventing Excel from applying incorrect default system settings.
Power Query provides robust tools for data transformation before it even loads into your spreadsheet. By utilizing the 'Using Locale' feature, you instruct Excel to read the incoming dates specifically as US-formatted (mm/dd/yy) dates, overriding your local system defaults.
Open Excel, navigate to the 'Data' tab on the top ribbon, select 'Get Data', choose 'From File', and then click 'From Text/CSV' to locate your bank file.
Once you select the file, a preview window will appear. Click the 'Transform Data' button at the bottom of this window to open the Power Query Editor.
In the Power Query Editor, locate the column containing the dates. Right-click on the column header, navigate to 'Change Type', and select 'Using Locale...' from the context menu.
In the dialog box, set the Data Type to 'Date' and change the Locale to 'English (United States)'. Click 'OK' to apply the formatting.
Click the 'Close & Load' button in the top-left corner of the Power Query ribbon. Your data will now appear in Excel with correctly recognized dates.
Use the Text to Columns Feature
If you have already imported or pasted the data into Excel, you can use the Text to Columns tool to retroactively correct the date parsing.
Effortlessly Import and Format Bank Files with WPS Spreadsheet
WPS Spreadsheet provides a seamless and user-friendly data import wizard, allowing you to correctly process complex date formats like mm/dd/yy on the fly without ever changing system settings.
- 1. Open the Data Import Wizard: Launch WPS Spreadsheet, go to the 'Data' tab, click on 'Import Data', and select your downloaded bank CSV or Text file.
- 2. Configure Delimiters: In the Text Import Wizard, select 'Delimited', choose the appropriate separator (like comma or tab), and click Next.
- 3. Set the Date Format: In the final step of the wizard, highlight your date column in the preview box, select the 'Date' data format, and choose 'MDY' from the dropdown list.
- 4. Complete Import: Click 'Finish' and choose where you want to place the data. WPS will automatically parse the dates correctly based on your selection.
Frequently Asked Questions
Why does Excel change my bank file dates to the wrong format?
Excel automatically relies on your computer's Windows regional settings to interpret date formats. If your computer is set to interpret dates as DD/MM/YYYY, but your bank exports files as MM/DD/YY, Excel gets confused and either misinterprets the month and day or leaves the entry as unformatted text.
Can I fix the date format by just changing the cell formatting?
No. If Excel initially interprets the date incorrectly during the import process, simply changing the cell format via 'Format Cells' will only change how the incorrect date is displayed. It will not fix the underlying data values. You must correct the parsing method using Power Query or Text to Columns.
How do I stop Excel from automatically converting data during import?
Instead of double-clicking a CSV file to open it directly in Excel, you should always use the 'Get Data' (Power Query) feature located in the Data tab. This prevents automatic conversion and allows you to manually define the correct data types for each specific column.
Does changing my Windows regional settings affect other programs?
Yes. Changing your system's regional settings to temporarily accommodate a specific bank file will alter how dates, times, and currencies are processed and displayed across all applications installed on your Windows operating system.
