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.
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.
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.
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.
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.
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.
3️⃣ 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.
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.
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.
Anna Grigoryan is a professional corporate accountant who provides accounting, bookkeeping and tax services to Small Business owners and individuals. She has more than ten years of professional experience in public accounting and a bachelor’s degree in Business Accounting. Anna is the CEO of Taxory, an accounting firm in Toronto area.