Downloads
Power Query MDate and timeAdvanced

DateTimeZone.SwitchZone

Moves a datetime with a time zone to a different fixed zone offset, given in hours and optional minutes.

Syntax
DateTimeZone.SwitchZone(dateTimeZone as datetimezone, hours as number, optional minutes as nullable number) as datetimezone

Returns: The same moment shown in the new offset, as a datetime with a time zone value.

What it does

DateTimeZone.SwitchZone shows a moment in another time zone by applying a fixed offset. A timestamp of 22:00 UTC becomes 14:00 at an offset of minus eight hours. The important limit is that the offset is fixed. It does not follow daylight saving time, so a zone that changes its clock twice a year needs a different offset in summer and in winter.

Syntax and arguments

Example

A bank reports payments in UTC. The controller wants to see which payments were received on the local business day in Pacific time, during a winter month when the Pacific offset is minus eight hours.

let
    Payment = #datetimezone(2026, 1, 15, 22, 0, 0, 0, 0, 0),
    Pacific = DateTimeZone.SwitchZone(Payment, -8),
    LocalDateTime = DateTime.From(Pacific),
    LocalDate = DateTime.Date(LocalDateTime)
in
    {LocalDateTime, LocalDate}

22:00 UTC on January 15, 2026 is 14:00 in Pacific standard time on the same day, so the payment belongs to January 15 for the local business day. The same UTC time in late October would be in daylight saving time, when Pacific is minus seven, so the offset would need to change.

Example: a second, different use

Convert a UTC timestamp to Eastern time in winter, using minus five hours, before grouping the transactions by day.

Things to watch

In business terms

Cut-off testing for international payments and bank files depends on the local date of each transaction. A wrong offset moves a payment to the wrong day, which is exactly the kind of error that auditors test for at period end.

Related functions

All Power Query M functions · View as Markdown · M basics