'%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 Convert QuickBooks Text Dates to Real Excel Dates
Question details
The user needs to convert text-formatted dates exported from QuickBooks into real date values in Excel to enable correct chronological sorting.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing and organizing financial data exported from QuickBooks into Excel.
- Observed behavior
- Dates exported from QuickBooks appear as text (e.g., 31-03-2022). Changing the cell format to Date does not convert the underlying text, causing Excel to sort the column alphabetically instead of chronologically.
Make sure your date column does not contain blank cells or a mix of different date formats, and expand your column width to ensure the converted dates display properly.
Use the Text to Columns Feature
The fastest and most reliable way to force Excel to recognize text dates as real date values.
The Text to Columns tool is typically used for splitting data, but it also has a powerful secondary function: it forces Excel to re-evaluate the data format of an entire column at once. By specifying the incoming text format (like Day-Month-Year), Excel effortlessly transforms the strings into native, sortable dates.
Highlight the entire column containing the QuickBooks text dates that you want to convert.
Go to the 'Data' tab on the Excel ribbon and click on 'Text to Columns' in the Data Tools group.
In the Convert Text to Columns Wizard, leave 'Delimited' selected and click 'Next' twice to skip directly to Step 3 of 3.
Under 'Column data format', select the 'Date' radio button. From the dropdown menu next to it, choose 'DMY' (Day-Month-Year) to match the incoming QuickBooks format (e.g., 31-03-2022).
Click 'Finish'. Excel will immediately convert the text strings into real dates. You can now sort the column chronologically.
Use a Date Conversion Formula
Use this method if you prefer to keep your original data intact and generate the converted dates in a separate column.
Quickly Clean Imported QuickBooks Data in WPS Spreadsheet
WPS Spreadsheet offers intuitive and fast data processing tools perfectly suited for converting imported text strings into usable numerical and date formats.
- 1. Highlight the date column: Open your exported QuickBooks file in WPS Spreadsheet and select the column with the text dates.
- 2. Launch Text to Columns: Navigate to the 'Data' tab on the top ribbon and click on the 'Text to Columns' icon.
- 3. Configure the conversion: Click 'Next' until you reach step 3. Choose 'Date' under Column data format and select 'DMY' from the dropdown list.
- 4. Apply changes: Click 'Finish'. Your data is instantly transformed into authentic, chronological dates.
Frequently Asked Questions
Why does changing the cell format to Date not fix the issue?
Changing the number format of a cell only alters how a recognized number or date is displayed visually. It does not force the spreadsheet to re-evaluate text strings into numerical values. The Text to Columns tool is required to fundamentally parse and re-evaluate the text string as a true date.
Can I prevent QuickBooks from exporting dates as text?
This depends on how you generate your reports in QuickBooks. Instead of exporting directly to Excel, you can try exporting the report as a CSV file. Then, use Excel's 'Get Data' (Power Query) feature to import the CSV, which allows you to define the column type as 'Date' during the import process itself.
How do I fix text dates if they are in Month-Day-Year (MDY) format?
You can use the exact same Text to Columns method. Simply highlight the column, go to Data > Text to Columns, skip to Step 3, choose Date, and change the dropdown selection from 'DMY' to 'MDY'. This tells the spreadsheet to read the first digits as the month instead of the day.
