If a date column sorts in a strange order or SUMIFS ignores it, the dates are probably text.
Test a cell with ISNUMBER: real dates are numbers, text dates are not. Convert them with Data > Text to Columns using the correct date order, with DATEVALUE for simple cases, or with DATE plus LEFT, MID and RIGHT when the format is unusual. Always confirm the day and month were not swapped.
=ISNUMBER(A2) returns FALSE.Count how many are affected: =SUMPRODUCT(--NOT(ISNUMBER(A2:A5000)),--(A2:A5000<>""))
Choosing the order is the important step. It tells Excel how to read the text, regardless of your computer's regional setting.
=DATEVALUE(A2)
DATEVALUE reads the text using your computer's regional date setting. It works when the export uses the same order as your computer, and gives wrong or #VALUE! results when it doesn't. Format the result as a date.
For text in the form 31.01.2026 (day, month, year):
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))
For 20260131 (year, month, day with no separators):
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
This method doesn't depend on regional settings at all, which makes it the safest for templates shared between countries. It assumes every value has the same fixed layout; check for single-digit days or months without leading zeros.
Exports such as "2026-01-31 14:22:05" carry a time. Convert, then keep the date part only:
=INT(DATEVALUE(LEFT(A2,10)))
When an export is day-first and the computer is month-first (or the reverse), Excel converts dates where both numbers could be a month (for example 05/03) into the wrong date, and leaves the rest as text. The column then looks half-fixed. After any conversion, check a few dates where the day is 12 or less against the source system.
The opposite problem also happens: Excel turns invoice numbers or account codes such as 3-12 into dates. Format those columns as Text before pasting, or import the file with Data > From Text/CSV and set the column type to Text.
If the same export arrives every month, build the conversion into the template: paste raw data into one sheet and let a formula column produce real dates. Add a check that counts any rows where the conversion failed, so a change in the export format is caught at once. See the month-end close checklist.
This is the classic UK–US problem: UK exports are usually day-month-year and US exports month-day-year. A file built in one country and opened in the other is the most common cause of swapped dates.
12 questions on how strong your close controls are. Free to use online.
The Month-End Close & Controls System tests the trial balance against the sum of reconciled balances, keeps every open item on one exception dashboard and blocks sign-off while exceptions remain open. Excel, no macros, one-time price.
It converts text it can read in your regional order and leaves the rest. When day and month are swapped, only dates with a day of 12 or less convert, and they convert wrongly.
No. Formatting changes how a number is displayed; it doesn't turn text into a number. Use one of the conversion methods.
Text to Columns with the correct date order, applied to the whole column.
Last updated 04 November 2026. General guidance for finance teams; check it against your own policies, systems and local rules.