As an accounting firm providing bookkeeping services to clients, we often have to work with Excel reports. When the client does not have QuickBooks Online (QBO) attached to the bank account, they usually provide us with a list of business transactions exported from their business bank account. However, in many cases, the dates in Excel are not recognized as a date data type but instead represented as text, which does not allow sorting by dates or changing date formats. There are multiple ways to resolve this issue, and below, we will share the one that works best for our company.

Transaction dates from bank export are not recognized

Excel fails to recognize dates from exported bank transactions.

In the screenshot, there is a sample extract from a bank account. When you look at the dates, they seem to be regular dates, but actually, they are text. Excel did not recognize that text as a date data type, so such operation as sorting is not available. One of the reasons for that may be a regionalization, where Canadian dates are expected to be in the format “dd/mm/yyyy” but the dates in Excel are in the format “mm/dd/yyyy” which is a US date format.

How do you know if the "date" is recognized as "date" data type or "text" type?

To determine whether Excel correctly recognized the date, select multiple cells with Date data. If it is not recognized, you will see a Count attribute that calculates the number of selected cells.

How to identify if Excel fail to recognize dates

However, when Excel correctly recognizes the date and converts it to a date data type in addition to Count, you will see the Average and Sum properties, as in the screenshot below.

Excel correctly recognized dates as Date data type

In addition, you can now format the date as you want, for example, as a Long Date, which is not available unless the date is correctly recognized.

Changing date format is possible in Excel
You can change Date format when Excel recognizes data correctly

Are you looking for a professional bookkeeping services?

Taxory is your reliable partner for professional bookkeeping services designed for Canadian corporations and small business owners. Our expertise in QuickBooks Online (QBO) positions us as your ideal partner in managing accounting, bookkeeping, and tax services. Let us take the burden off your shoulders. Explore our bookkeeping solutions and contact one of our accountants for a complimentary, no-obligation consultation.

Learn More

Solving Excel’s Date recognition problem.

When trying to find a solution for Excel date recognition on the Internet, the most common is to use the DATEVALUE function, which is supposed to convert the date stored as text to a number that Excel recognizes as a date. For example, the formula =DATEVALUE("2/28/2023") returns 44985. However, we did not have good results with the function because it recognized the date only in a specific format, in the case of Canada in the format “yyyy-mm-dd,” and the export we got from the client was in a different format. You can learn more about the DATEVALUE function at the Microsoft support website.
After some experiments, we found the best way to convert dates as text to Date format using the “Text to Columns” functionality. That works well in all cases, with any date format, so that is the way we recommend to you.

The step-by-step guide for date conversion from text to Date format in Excel:

1️⃣ First, Select the dates you want to convert to Date format and press the “Text to Columns” button under the “Data” tab in the ribbon.

Select the dates you want to convert to Date format and press the "Text to Columns" button under the "Data" tab in the ribbon
2️⃣ Choose the Delimited data type and press the “Next” button.
Choose the Delimited data type and press the “Next” button.

3️⃣ Uncheck all delimiters and press the “Next” button.

Uncheck all delimiters and press the next button.

4️⃣ Choose the “Date” column data format and the format you have. In our case, it is “MDY” which stands for Month-Day-Year.

Choose the "Date" column data format and the format you have. In our case, it is "MDY" which stands for Month-Day-Year.

5️⃣ Now press the “Finish” button, and Excel correctly recognizes and converts text to date. As a result, you can sort by date and change date formats. You notice the leading zeros for the month and days, and once you select a group of dates, you can see the Average and Sum, which tells that the dates are recognized in a Date format.

Now dates are correctly recognized in Excel

Professional bookkeeping services for your corporation!

Choose Taxory for professional bookkeeping services and free up valuable time as a business owner. Our bookkeeping solutions are designed to streamline your financial processes, allowing you to focus on what you do best-growing your business. And with each package, you’ll also receive complimentary corporate tax preparation and filing. Contact us today, and we’ll happily discuss how we can support your business needs.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.