DateTimeZone.ToText - DateTimeZone Function

DateTimeZone.ToText is a Power Query M function that converts a datetimezone value to a textual representation based on the provided format and culture options. The function returns a text value representing the datetimezone in the specified format.

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

Syntax

DateTimeZone.ToText(
   dateTimeZone as nullable datetimezone,
   optional options as any,
   optional culture as nullable text,
) as nullable text

Argument	Attribute	Description
Time		The DateTimeZone value to transform into text.
Options	Optional	This 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.
Culture	Optional	This is an optional argument used for legacy workflows only. You can specify the culture as a text value here.

Description

The DateTimeZone.ToText function transforms a DateTimeZone 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.

To support legacy workflows, options and culture may also be text values. This has the same behavior as if options = [Format = options, Culture = culture].

Examples

To better understand how DateTimeZone.ToText works, let’s look at a few examples.

This function can convert the datetimezone value #datetimezone( 2010, 12, 31, 01, 30, 25, 2, 0 ) into a text value.

// Output: "12/31/2023 1:30:25 AM +02:00"
DateTimeZone.ToText(
   #datetimezone( 2023, 12, 31, 01, 30, 25, 2, 0 )
)

This is particularly useful when you need to display the date and time in a human-readable format, such as in a report or a user interface. However, when the output of this operation does not satisfy your needs you should look for alternatives.

And instead of extracting parts of the DateTimeZone value and combining them back together, why not use Custom Format Strings?

Custom Format Strings

Custom Format Strings instruct the DateTimeZone.ToText function to returns specific items from a DateTimeZone value.

For instance, to return the current time in GMT format you can make use of the RFC1123 pattern:

// Output: "Sat, 30 Dec 2023 23:30:25 GMT"
DateTimeZone.ToText(
  #datetimezone( 2023, 12, 31, 01, 30, 25, 2, 0 ),
  "r" 
)

This operation moved the time back two hours to get in line with the ‘GMT’. It also added the GMT designator at the end of the string. In another case you may want to return the text ‘UTC’ instead.

Since the format strings don’t support this output, you will have to manually create it.

// Output: "2023-12-31 01:30:25 UTC"
DateTimeZone.ToText(
  #datetimezone( 2023, 12, 31, 01, 30, 25, 2, 0 ),
  "yyyy-MM-dd HH:mm:ss 'UTC'" 
)

In this example the single quotes function as escape character, so you can provide manual text at the end of the string.

Supported Format Strings

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

Format	Description	12/31/2023 1:30:25 -02:00	12/31/2023 1:30:25 +10:00
%z	UTC offset in hours	-2	10
zz	UTC offset in hours with leading zero	-02	10
%K, zzz	UTC offset in hours and minutes	-02:00	+10:00
r, R	RFC1123 pattern	Sun, 31 Dec 2023 03:30:25 GMT	Sat, 30 Dec 2023 15:30:25 GMT
o, O	Round-trip date/time pattern	2023-12-31T01:30:25.0000000-02:00	2023-12-31T01:30:25.0000000+10:00

Note that the above table contains the format strings that are only relevant to DateTimeZone values. Since a DateTimeZone value contains a date value and a time value you can apply any custom format string that is relevant to those value types as well. You can find them in the Date.ToText, the Time.ToText and the DateTime.ToText article.

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.

Culture

Next to custom formatting strings, the culture on your machine can also make an impact on the function output. For instance, let’s say you want to return a custom format for the German culture:

// Output: "30 Dez 2023 02:04:50.369730 -08:00"
DateTimeZone.ToText( 
  #datetimezone( 2023, 12, 30, 2, 4, 50.36973, -8,0 ), 
  [Format="dd MMM yyyy HH:mm:ss.ffffff zzz", Culture="de-DE"] 
)

The start of the output shows “30 Dez”, which is the german short month format. Being able to return such an output is useful when you need to display the date and time in a specific format that is not the default format in your system or the system of others.

Another example uses the ISO 8601 pattern:

// Output: "2023-02-08T03:45:12.0000000+02:00"
DateTimeZone.ToText( 
  #datetimezone( 2023, 2, 8, 3, 45, 12, 2, 0 ),
  [Format="O", Culture="en-US"] 
)

ISO 8601 is an international standard covering the exchange of date and time-related data. It is useful when you need to display the date and time in a format that is universally recognized and understood.

DateTimeZone.ToText Function with Custom Format in Power Query M

In conclusion, DateTimeZone.ToText allows you to convert a datetimezone value into a custom string. It’s especially useful when dealing with data across different timezones.

Learn more about DateTimeZone.ToText in the following articles:

Mastering Custom Format Strings in Power Query
This guide shows how to use custom format strings to format values in the M language. » Read more

Other functions related to DateTimeZone.ToText are:

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