Time.ToText

Updated on

Time.ToText is a Power Query M function that returns a textual representation of a time value, with optional formatting and culture options. The function returns the text representation of the provided time value.

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

Syntax

Time.ToText(
   time as nullable time,
   optional options as any,
   optional culture as nullable text,
) as nullable text
ArgumentAttributeDescription
TimeThe time value to transform into text.
OptionsOptionalThis is an optional argument where you can provide a record parameter with additional instructions. The Format field is used to specify the output format of the transformation. You can also provide a Culture field to ensure the transformation supports local formats. For example: [ Format = “hh:mm:ss tt”, Culture = “nl-NL” ]. If you omit this argument, the default format used will align with the culture of your system, using Culture.Current.
CultureOptionalThis is an optional argument used for legacy workflows only. You can specify the culture as a text value here.

Description

The Time.ToText function transforms a time value into a readable text form. You can influence how the function performs by giving it specific options in a record format. Two key options you can set are Format and Culture. Each has a considerable effect on the function’s output.

Fields in the Record:

  • Format: This is a text field that sets the time display format. A full list of supported formats for time values is available later in this article. If you don’t provide this field, or if you set it to null, the function will use a default format, which is determined by the Culture setting.
  • Culture: This option comes into play in two scenarios. First, when you have provided a Format, the Culture setting modifies certain aspects of that format. For example, the afternoon time in the United States (“en-US”) will display “PM,” whereas in the Netherlands (“nl-NL”) it won’t display anything. Second, if Format is set to null, Culture sets the default time format. If Culture is also set to null or left blank, the system’s current culture setting will be used.

Examples

Let’s look at some examples to better understand how Time.ToText works.

Basic Conversion

Let’s start with a basic example to illustrate how the Time.ToText function works. Suppose you have a time value #time(12,30,55). You can convert this time to text by using the following code:

// Output: "12:30"
Time.ToText( #time(12,30,55 ) )

As shown, the ‘minutes’ part is missing in the output, making it useful when you only want to focus on a specific time frame.

Using Custom Format Strings

To control the output of the function, you can use custom format strings.

Specify Hours, Minutes and Seconds

Where we were missing the seconds component before, we can still add it in our custom format string.

// Output: 12:30:55
Time.ToText( #time(12,30,55 ), [ Format = "hh:mm:ss" ] )

Or use the short-hand syntax for the same result:

// Output: 12:30:55
Time.ToText( #time(12,30,55 ), "hh:mm:ss" )

Adding AM/PM Designators

Another example shows how you can return the AM/PM designator to your value by including the custom format ‘tt‘ at the end of your formatting string.

// Output: "12:30:55 PM"
Time.ToText( #time(12,30,55 ), [ Format = "hh:mm:ss tt" ] )

Supported Format Strings

Customizing the output involves understanding the available custom format strings. Here’s a table that contains the format strings that are supported for time values:

FormatDescription01:45:30.885949223:01:04
tShort time pattern01:4523:01
TLong time pattern01:45:3023:01:04
%hHour as a single digit (0-12)111
%HHour as a single digit (0-23)123
hhHour with leading zero (01 -12)0111
HHHour with leading zero (00-23)0123
%mMinute as a single digit (0-59)451
mmMinute with leading zero (00-59)4501
%sSecond as a single digit (0-59)304
ssSecond with leading zero (00-59)3004
%tThe first character of AM/PMAP
ttThe AM/PM designatorAMPM
%fTenths of a second80
ffHundredths of a second8800
fffMilliseconds885000
ffffTen-thousandths of a second88590000
fffffHundred thousandths of a second8859400000
ffffffMillionths of a second885949000000
fffffffTen-millionths of a second88594920000000
%FIf not zero, tenths of a second8
FFIf not zero, hundredths of a second88
FFFIf not zero, milliseconds885
FFFFIf not zero, ten-thousandths of a second8859
FFFFFIf not zero, hundred-thousandths of a second88594
FFFFFFIf not zero, millionths of a second885949
FFFFFFFTen millionths of a second8859492

To learn the possibilities and see what other format strings are there, you can have a look at this Overview of all Custom Format Strings.

Considering Culture

The Culture option significantly impacts how time is formatted. For instance, the long-time pattern will differ based on local rules.

AM/PM Designator

Using the Dutch culture (nl-NL), the time is formatted in 24-hour intervals, and there’s no AM/PM designator:

// Output: 23:30:55
Time.ToText( #time(23,30,55 ), [Format = "T", Culture = "nl-NL"] )

Switching the culture to American (en-US) will add an AM/PM designator and use a 12-hour clock:

// Output: 11:30:55 PM
Time.ToText( #time(23,30,55 ), [Format = "T", Culture = "nl-NL"] )

Here’s some more example where you can see the difference:

Time.ToText with custom format string and culture in power query m

The Time.ToText function is a useful function that allows you to convert time into a custom text format. It proves especially useful when working with data where you want to include a time as text. By understanding its custom format strings and culture settings, you can easily adapt the output to suit your specific requirements.

Learn more about Time.ToText in the following articles:

Other functions related to Time.ToText are:

BI Gorilla Blog

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