DateTime.AddZone

DateTime.AddZone is a Power Query M function that adds timezone information to the date(time) value, including timezoneHours and optionally timezoneMinutes.

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

Syntax

DateTime.AddZone(
   dateTime as nullable datetime,
   timezoneHours as number,
   optional timezoneMinutes as nullable number,
) as nullable datetimezone

Description

DateTime.AddZone adds timezone information to the dateTime argument, which can be of type datetime. The function includes timezoneHours and, optionally, timezoneMinutes, specifying the desired offset from UTC time. This allows for precise adjustments to the datetime value to reflect a specific timezone.

Examples

Let’s say you want to add the timezone UTC+3:30 (3 hours and 30 minutes past UTC) to a datetime value. An easy way to do that is by using:

// Output: #datetimezone( 2024, 10, 25, 10, 50, 2, 3, 30 )
DateTime.AddZone( #datetime( 2024, 10, 25, 10, 50, 2 ), 3, 30 )

This adjusts the datetime value to reflect the timezone that is 7 hours and 30 minutes ahead of UTC.

Setting the Timezone to UTC+0:00

To set the timezone to UTC+0:00 (UTC itself):

// Output: #datetimezone(2023, 1, 1, 0, 0, 0, 0, 0)
DateTime.AddZone(#datetime(2023, 1, 1, 0, 0, 0), 0, 0)

This sets the datetime value to reflect UTC without any offset.

Adjusting for Daylight Saving Time (UTC+1:00 in Winter and UTC+2:00 in Summer)

If you need to adjust for daylight saving time, for instance, UTC+1:00 in winter and UTC+2:00 in summer:

// Winter (UTC+1:00)
// Output: #datetimezone(2024, 1, 1, 12, 0, 0, 1, 0)
DateTime.AddZone(#datetime(2024, 1, 1, 12, 0, 0), 1, 0)

// Summer (UTC+2:00)
// Output: #datetimezone(2024, 7, 1, 12, 0, 0, 2, 0)
DateTime.AddZone(#datetime(2024, 7, 1, 12, 0, 0), 2, 0)

Related functions

Other functions related to DateTime.AddZone are:

• DateTime.From
• DateTime.FromFileTime
• DateTime.FromText
• DateTime.ToRecord
• DateTime.ToText

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