#datetimezone - DateTimeZone Function

#datetimezone is a Power Query M function that creates a datetimezone value from numbers representing the year, month, day, hour, minute, (fractional) second, (fractional) offset-hours, and offset-minutes.

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

Syntax

#datetimezone(
   year as number,
   month as number,
   day as number,
   hour as number,
   minute as number,
   second as number,
   offsetHours as number,
   offsetMinutes as number,
) as datetimezone

Description

The #datetimezone function creates a datetimezone value from specified numbers representing the year, month, day, hour, minute, (fractional) second, (fractional) offset-hours, and offset-minutes.

Criteria for Each Component:

Year: Must be between 1 and 9999 (inclusive).
Month: Must be between 1 and 12 (inclusive).
Day: Must be between 1 and 31 (inclusive).
Hour: Must be between 0 and 23 (inclusive).
Minute: Must be between 0 and 59 (inclusive).
Second: Must be between 0 and less than 60.
Offset-hours + Offset-minutes / 60: Must be between -14 and 14 (inclusive).

If any of these conditions are not met, the function will return an error message stating: “The DateTimeZone operation failed because the resulting value falls outside the range of allowed values.”

Examples

Returns the datetimezone value of June 10, 2023 (date) 04:08:12 (time) -01:30 (offset).

#datetimezone( 2023, 6, 10, 4, 8, 12, -1, 30 ) // Returns 10/06/2023 04:08:12 -01:30

You can easily extract the zonehours, zoneminutes, or turn the value into a different format.

DateTimeZone.ZoneHours(   #datetimezone( 2023, 6, 10, 4, 8, 12, -1, -30 ) ) // Returns - 1

DateTimeZone.ZoneMinutes( #datetimezone( 2023, 6, 10, 4, 8, 12, -1, -30 ) ) // Returns - 30

DateTimeZone.RemoveZone(  #datetimezone( 2023, 6, 10, 4, 8, 12, -1, -30 ) ) // Returns 10/06/2023 04:08:12

DateTimeZone.ToUtc( #datetimezone( 2023, 6, 10, 4, 8, 12, -1, -30 ) ) // Returns 10/06/2023 05:38:12 +00:00

Other functions related to #datetimezone are:

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

Syntax

Description

Examples

Related functions