Time.FromText - Time Function

Time.FromText is a Power Query M function that creates a time value from a text representation, with optional formatting and culture options. The function returns the time value corresponding to the provided textual representation.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Time.FromText(
   text as nullable text,
   optional options as any,
) as nullable time

Argument	Attribute	Description
time		The text value to transform into a time value.
options	optional
culture	optional	The culture argument enables the specification of a Culture code (e.g., “nl-NL” or “en-US”) to align transformations with local formatting conventions. If this argument is omitted, functions default to Culture.Current, which reflects the system’s regional settings.

Description

Time.FromText converts a string into a time value. It optionally accepts a record parameter named options for specifying detailed properties. This function offers two main fields within the options record:

Format Field: This field, holding a text type, determines the format applied during conversion. If omitted or set to null, the function attempts to interpret the time format in a general manner. Further information about format patterns can be accessed via the Time.ToText function or through Microsoft’s resources (this link and that link).
Culture Field: This field comes into play when the Format field is not null. It adapts certain format aspects based on cultural settings. For example, under the "en-US" culture, the "tt" format represents either “AM” or “PM”, contrasting with the "ar-EG" culture, where "tt" stands for “ص” or “م”. The default formatting is governed by Culture.Current when this field is absent or null.

The function also retains compatibility with older workflows by accepting a text value for options, equating to options = [Format = null, Culture = options].

Examples

Let’s see some examples of how you can use the Time.FromText function.

Basic Usage

To extract the time from a text string like “04:15:50.442”, you can use the Time.FromText function. Here’s how:

Time.FromText( "04:15:50.442" ) // Output: #time( 4, 15, 50.442)

When the string is in the format “hour:minute:second.fraction”, the function extracts the time correctly. It also works with simpler formats:

Time.FromText("04:15:50") // Output: #time( 4, 15, 50 ) 
Time.FromText("04:15")    // Output: #time( 4, 15, 0  )
Time.FromText("4:15")     // Output: #time( 4, 15, 0  ) 
Time.FromText("4:1")      // Output: #time( 4, 1 , 0  )

However, be careful when providing only the hour portion. The following code returns an error: “We couldn’t parse the input provided as a Time value”.

Time.FromText( "4" ) // Output: #time( 4, 0, 0)

To use only the hour, you need a different format, which we’ll discuss next.

Format Without Colons

You can convert time values from text strings even if they don’t use colons to separate the hours, minutes, and seconds:

Time.FromText("041530.442") // Output: #time(4, 15, 30)
Time.FromText("041530")     // Output: #time(4, 15, 30)
Time.FromText("0415")       // Output: #time(4, 15, 0 )
Time.FromText("04")         // Output: #time(4, 0 , 0 )

Each example shows how the text is interpreted into hours, minutes, and seconds.

Format using AM/PM designator

You can also specify the time of day using AM or PM. AM stands for “before noon” and PM stands for “after noon.” Here are some examples:

Time.FromText( "04:15 AM" ) // Output: #time( 4 , 15, 0)
Time.FromText( "04:15 PM" ) // Output: #time( 16, 15, 0)

In these examples, AM and PM help differentiate between 4:15 in the morning and 4:15 in the afternoon. This method is useful when you need to be clear about whether the time is in the morning or the afternoon.

To see all of these formats in action, here’s an overview of all the different strings in a column. The column next to it transforms the text into time values:

Time.FromText Extracts Time Value From A String in Power Query M

Other functions related to Time.FromText are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/time-fromtext