DataFormat.Error: We couldn't parse the input provided as a DateTime value
By Tahir Nas · Examples run in our simulator · Updated
DataFormat.Error: We couldn't parse the input provided as a DateTime value.
The text isn't a date and time the culture recognises. Timestamps from logs and APIs often use formats with a T, a zone suffix or a 24-hour clock that need an explicit culture or format.
Common causes
- Placeholder text such as "pending" in a timestamp column.
- A culture mismatch on the date part.
- Unusual separators or a trailing zone abbreviation.
How to fix it
- Give the format: DateTime.FromText(x, [Format = "dd/MM/yyyy HH:mm", Culture = "en-GB"]).
- Use DateTimeZone.FromText for values ending in Z or +01:00, then DateTimeZone.RemoveZone.
- Replace placeholders with null before converting.
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:
DateTime.FromText("pending")Fixed:
DateTime.FromText("2026-03-04T09:30:00")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.