Level 5 · Dates stored as text, in three formats

Edward’s sheet merges three exports. Only one of them wrote real dates. To Excel, a date is a number (the count of days since 1900, which chapter 4 covered), shown with a date format. Text that looks like a date is still text: it sorts alphabetically, MIN and MAX skip it, and a filter by month can’t see it.

The three formats in this sheet

Channel	Example	What it is
web	2025-06-09 in C3, right-aligned	a real date (the number 45817 with a date format)
app	09.06.2025 in C2, left-aligned	text, day.month.year
marketplace	6/9/2025 in C6, left-aligned	text, month/day/year

We built this sheet the way merged CSVs arrive: the dates are the store’s real order dates, each written in the format its channel’s export uses. The alignment tell from level 3 works here too. So does the count: =COUNT(C2:C36) returns 11, the web orders. The other 24 dates are text.

DATEVALUE reads text your computer understands

DATEVALUE(text) turns date text into the date’s serial number, using your computer’s regional settings. On a US setup, =DATEVALUE("6/9/2025") returns 45817, June 9. It also reads 2025-06-09. Give the result a date format (Ctrl+1, Date) and it shows as a date.

Two traps sit in that sentence. First, DATEVALUE("09.06.2025") returns #VALUE! on a US setup: dots aren’t a date separator there. Second, on a day-first setup (most of Europe), DATEVALUE("6/12/2025") doesn’t fail: it returns 6 December. Every date in this sheet has a day of 12 or less, so a wrong reading would look perfectly valid. A silent swap is worse than an error.

DATE builds a date from its parts

When the format is known, cut it apart and rebuild it. In 09.06.2025 the day is the first two characters, the month the two after the dot, and the year the last four:

=DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2))

DATE(year, month, day) doesn’t depend on regional settings at all, so this works on any computer. The text pieces "2025", "06" and "09" are converted to numbers on the way in.

One formula for the column

Test what kind of cell you have, from the most certain case down:

E2   =IF(ISNUMBER(C2),C2,IF(ISNUMBER(FIND(".",C2)),DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2)),DATEVALUE(C2)))

FIND(".",C2) returns a position when there is a dot and #VALUE! when there isn’t, so ISNUMBER(FIND(...)) is a clean “has a dot” test. Fill it down: G2, =MIN(E2:E36), shows 2025-06-09, and G3, =MAX(E2:E36), shows 2025-06-12, which matches the four days Edward exported. That check is the point. If a date had been misread, the range would stretch to December.

In Excel itself

For a whole column in a single format, Excel has a tool: select the column, Data > Text to Columns, click Next twice, and in step 3 choose Date and the order the text uses (DMY for the app’s dates, MDY for the marketplace’s). Excel converts the column in place. Then Ctrl+1 > Date picks how it’s displayed. Changing the format alone, without converting, does nothing to text. With mixed formats in one column, the formula is the safer route.

Common mistakes

• Formatting text as a date. Ctrl+1 changes how numbers look; text stays text.
• Trusting DATEVALUE on slashes from another country. Know which order the source uses before converting.
• Sorting before converting. Text dates sort as text: 6/10/2025 comes before 6/9/2025.
• Not checking the range. MIN and MAX of the clean column catch a swapped day and month in seconds.