DataFormat.Error: We couldn't parse the input provided as a Date value
By Tahir Nas · Examples run in our simulator · Updated
DataFormat.Error: We couldn't parse the input provided as a Date value.
Text was converted to a date using a culture that reads it differently. 31/12/2026 is valid day-first (en-GB) but not month-first (en-US), where there is no month 31.
Common causes
- Day-first dates converted with an en-US culture (the default on many machines).
- Mixed formats in one column, such as 2026-03-04 and 04/03/2026.
- Extra text: "31/12/2026 (est.)".
How to fix it
- Pass the source culture: Table.TransformColumnTypes(…, {{"Date", type date}}, "en-GB") or Date.FromText(x, [Culture = "en-GB"]).
- For known layouts, give the format: Date.FromText(x, [Format = "dd/MM/yyyy"]).
- For mixed columns, try one format and fall back to another with try … otherwise.
Find the step that fails
- Open the query in the Power Query editor (Excel: Data → Queries & Connections → Edit; Power BI Desktop: Transform data).
- Click the steps in Applied Steps from the top. The first step that shows the error is where it starts; later steps only repeat it.
- Read that step's formula in the formula bar (View → Formula Bar if it's hidden) and compare it with the columns and values the previous step produced.
Minimal example
Raises the error:
Date.FromText("31/12/2026", [Culture = "en-US"])Fixed:
Date.FromText("31/12/2026", [Culture = "en-GB"])Both snippets are run by the Power Query Exercises simulator on every build: the first must raise DataFormat.Error, the second must run.
Practise avoiding it
- intermediate~8 minFree
Convert day-first dates with the right locale (consultancy)
Claim dates are written 05/03/2026 meaning 5 March. Convert them to real dates using the en-GB culture.
- intermediate~7 minFree
Read amounts written with a decimal comma (consultancy)
A German export writes 1.234,56 for one thousand two hundred thirty-four point five six. Convert it with the de-DE culture.
- beginner~7 minPro
Add year, month and quarter columns (kite shop)
Convert OrderDate to a date, then add Year, Month (name) and Quarter (number).
- intermediate~8 minPro
Calculate days to close each ticket (telecom)
Convert Opened and Closed to dates and compute whole days between them; open tickets stay null.
Guides
- Change type using locale: dates and numbers from other countries
- Power Query date functions cheat sheet (tested)