Date.FromText
DateConverts a text representation of a date to a date value.
Syntax
Date.FromText(text as nullable text, optional options as any) as nullable dateParameters
| Name | Type | Required | Description |
|---|---|---|---|
text | text | Yes | A text string representing a date, such as "2024-03-15" or "March 15, 2024". |
options | any | No | A record or culture text string. Supported record fields: Format (custom format string, e.g., "yyyy-MM-dd") and Culture (e.g., "en-US"). |
Return Value
date — A date value parsed from the given text, or null if the input is null.
Remarks
Date.FromText parses a text value into a date. Without options, it attempts common ISO 8601 formats (e.g., "2024-03-15"). Use the options parameter — a record with Format and/or Culture fields — to handle locale-specific or non-standard date strings. Format strings follow .NET custom date format conventions (e.g., "dd/MM/yyyy" for UK-style dates, "MM/dd/yyyy" for US-style).
Always specify both Format and Culture when parsing non-ISO date strings to avoid ambiguity. For example, "03/04/2024" could be March 4 or April 3 depending on locale. Without a Format, the parser may succeed but silently produce an incorrect date.
If the text is null, the function returns null. If the text cannot be parsed with the given format, the function raises an error — so wrap in try ... otherwise null when the input may contain unparseable values. For datetime strings (including time components), use DateTime.FromText instead.
Examples
Example 1: Parse ISO 8601 date strings
Table.TransformColumns(
Table.AddColumn(
Table.SelectColumns(Table.FirstN(Sales, 4), {"OrderID"}),
"DateText",
each Text.From(Date.Year([OrderDate])) & "-" &
Text.PadStart(Text.From(Date.Month([OrderDate])), 2, "0") & "-" &
Text.PadStart(Text.From(Date.Day([OrderDate])), 2, "0"),
type text
),
{{"DateText", each Date.FromText(_, "en-US"), type date}}
)OrderID | DateText | |
|---|---|---|
| 1 | 1 | 1/15/2024 |
| 2 | 2 | 1/18/2024 |
| 3 | 3 | 2/1/2024 |
| 4 | 4 | 2/10/2024 |
Example 2: Parse a locale-specific date format
#table(
type table [TextDate = text, ParsedDate = date],
{{"15/03/2024", Date.FromText("15/03/2024", [Format="dd/MM/yyyy", Culture="en-GB"])}}
)TextDate | ParsedDate | |
|---|---|---|
| 1 | 15/03/2024 | 3/15/2024 |
Example 3: Handle unparseable values safely
#table(
type table [RawDate = text, ParsedDate = date],
{
{"2024-01-15", try Date.FromText("2024-01-15") otherwise null},
{"N/A", try Date.FromText("N/A") otherwise null},
{"2024-03-05", try Date.FromText("2024-03-05") otherwise null}
}
)RawDate | ParsedDate | |
|---|---|---|
| 1 | 2024-01-15 | 1/15/2024 |
| 2 | N/A | null |
| 3 | 2024-03-05 | 3/5/2024 |