DATEVALUE
Converts a date written as text into a date value.
DATEVALUE(<date_text>)Returns: A date.
What it does
DATEVALUE turns a date that is stored as text into a real date, so it can be sorted, filtered and used in time intelligence. It is common when a bank or ERP export delivers dates as text. The result depends on the regional settings of the machine running the model, so text dates in a different order (for example 10/06/2026, which means June 10 in one country and October 6 in another) can be read wrongly. For a file with a known layout, splitting the text into parts is safer.
Syntax and arguments
- date_text: the text that holds the date, such as "2026-10-29" or "10/29/2026". Required.
Example
A bank export stores the posting date as text in the form yyyy-mm-dd, which is an unambiguous layout. A calculated column converts the text into a date, using the fixed positions of the year, month and day.
Posting Date =
DATE(
VALUE(LEFT('Bank_Export'[PostedText], 4)),
VALUE(MID('Bank_Export'[PostedText], 6, 2)),
VALUE(RIGHT('Bank_Export'[PostedText], 2))
)
LEFT takes the first four characters for the year, MID takes the two characters starting at position 6 for the month, and RIGHT takes the last two characters for the day. VALUE turns each part into a number, and DATE builds the date. Because no regional setting is involved, the result is the same on any machine.
Example: a second, different use
When the export is known to hold dates in the form 2026-10-29, DATEVALUE is a short alternative to the split above.
Posting Date = DATEVALUE('Bank_Export'[PostedText])
Things to watch
- Test DATEVALUE on a sample from each export before you trust it. A date such as 03/04/2026 can be read two ways.
- A text value that is not a date returns an error. Clean blank and odd rows first, or wrap the call in IFERROR.
- Keep the converted date in a calculated column. Converting text inside every measure is slow and easy to get wrong.
- Once the column is a true date, mark the Calendar as the date table and relate it. Text dates do not join to a date table.
In business terms
DATEVALUE is part of every data cleaning step for bank files, payment exports, subledger extracts and spreadsheets pasted into a model. Converting dates correctly is the first control in a reconciliation: if the dates are wrong, the cut-off tests and aging reports are wrong too.