Daylight Saving Time And Time Zones In M

Quite a few people (Ken Puls, Reza Rad and today Chris Koester) have blogged about how the M date/time zone functions work in Power Query/Power BI, and the related problem of turning a UTC time into the actual time in any given location taking Daylight Saving Time changes into account. I don’t have much to add to what they have written, but I did learn something new about this subject from a customer last week: it is possible to do the conversion in pure M if the time zone you’re converting to is your PC’s own local time zone.

Here’s an example. In 2017 the clocks in the UK went forward at 1am on Sunday March 26th. Given the following table with two times in it, one of which is 1pm on Saturday March 25th and one of which is 1pm on Sunday March 26th:


…if you assume that these times are both UTC, here’s a query that returns the correct equivalent local time for me (because my PC is set to UK time) for both of them:

    //Read data from Excel table
    Source = 
    //Change the type of the column to datetime
    ChangeType1 = 
		{{"Time", type datetime}}),
    //Turn this into a UTC time
    ConvertToUTC = 
		each DateTime.AddZone([Time],0)),
    //Change the data type to datetimezone
    ChangeType2 = 
		{{"UTC", type datetimezone}}),
    //Convert to local PC time
    ConvertToLocal = 
		each DateTimeZone.ToLocal([UTC])),
    //Change type to datetimezone
    ChangeType3 = 
		ConvertToLocal ,
		{{"Local", type datetimezone}})


Here’s a brief explanation of what the query does:

  • First it reads the times from the Excel table and sets the Time column to be datetime data type
  • It then creates a new column called UTC and then takes the values in the Time column and converts them to datetimezone values, using the DateTime.AddZone() function to add a time zone offset of 0 hours, making them UTC times
  • Finally it creates a column called Local and converts the UTC times to my PC’s local time zone using the DateTimeZone.ToLocal() function

As you can see from the output table, the DateTimeZone.ToLocal() function has correctly handled the UK Daylight Saving Time change by converting the UTC time 13:00 on March 25th 2017 to 13:00 UK time, and converting the UTC time 13:00 on March 26th 2017 to 14:00 UK time.

Frustratingly there doesn’t seem to be a way to convert a UTC time to the correct local time in any given time zone in M – the DateTimeZone.SwitchZone() function just adds/removes hours without taking Daylight Saving Time into account, so it’s not really very useful. Apart from all the blog posts linked to at the start of this post, when I posted this question on the Power Query forum I got a very helpful answer from Marcel Beug with a completely different approach to solving the problem here, although it’s still not a straightforward one. If anyone from Microsoft is reading, it would be great to have a new M function to handle this requirement!

15 responses

  1. Pingback: #Excel Super Links #15 – shared by David Hager | Excel For You

  2. Great post Chris! When using this with Power BI, would it evaluate once at the Gateway, at the Power BI server, or would it evaluate at the client (Assuming these were all at different Time zones?

  3. Hi Chris,
    I noticed it is a year from the last comment on this subject. I have been trying to resolve how best to handle Daylight Saving Hours but without any luck so far. My reports need to work on “service” and therefore when localtime is same as UTC.

    I have managed to add a daylight savings indicator on the DAX built CalendarAuto() Dates table but really need this at the power query stage to enable me to build a model relationship between Dates table and the imported data(adjusted date).not the UCT date.

    With UCT date/time of 31 March 2020 23:00, actually adjusting to 1 April 2020 00:00 hours it makes quite a difference. i.e dates not only wrong day but wrong month.

      • I have managed to come up with the following simply query to handle BST (Daylight Saving) in the UK.

        This takes the date as a parameter, needs to be in Date/Time format, and checks to see if the date is between the last Sunday in March and last Sunday in October (UK Daylight Saving) for the year of the date provided. If it does then it adds 1 hour to the date and returns it to be used. I got the basis for this from other similar US versions I found.

        AdjustedDate = (InDate as any) =>
        OutDate = if InDate >= Date.StartOfWeek(#datetime(Date.Year(InDate),03,31,0,0,0),1) and
        InDate <= Date.StartOfWeek(#datetime(Date.Year(InDate),10,31,0,0,0),1)
        then InDate + #duration(0,1,0,0)
        else InDate

  4. I’ve just come across this problem in 2022 – there still doesn’t seem to be a built-in solution.
    And I then found that Gordon Parmenter’s solution, while clever, is slightly incorrect and goes from Monday to Monday and not the Last Sunday of March to the last Sunday of October, and ignores the times the clocks officially change (1am GMT forward, 2am BST backward). So I came up with this which I’m pretty certain is correct:
    BSTAdjustedDateTime = (_myDate as datetime) =>
    _BSTCheckDate =
    if _myDate >= Date.StartOfWeek(#datetime(Date.Year(_myDate),03,31,0,0,0),Day.Sunday) + #duration(0,1,0,0) and
    _myDate < Date.StartOfWeek(#datetime(Date.Year(_myDate),10,31,0,0,0),Day.Sunday) + #duration(0,1,0,0)
    then _myDate + #duration(0,1,0,0)
    else _myDate

  5. Nice improvement on taking account of official change in the clocks. I Didn’t need that precision on my original solution as I had a date rather than time issue. i.e Midnight coming up as 23:00 the day before.

    My solution was built in my first few months of Power Query/BI endeavours, and after some frustration at not finding anyone had done this before. Now almost 2 years on I have learned and forgotten a lot along the way but did fix the Monday issue once we got to October and I realised the error. Now use Date.StartOfWeek(#datetime(Date.Year(InDate),10,31,0,0,0),0) rather than Date.StartOfWeek(#datetime(Date.Year(InDate),10,31,0,0,0),1).

  6. Great stuff. The blogpost was a while ago, and I’m sure you have a solution for it. For others interested, the code I like using is:

      UTC_DateTimeZone = DateTimeZone.UtcNow(),
      UTC_Date         = Date.From(UTC_DateTimeZone),
      StartSummerTime  = Date.StartOfWeek(#date(Date.Year(UTC_Date), 3, 31), Day.Sunday),
      StartWinterTime  = Date.StartOfWeek(#date(Date.Year(UTC_Date), 10, 31), Day.Sunday),
      UTC_Offset       = if UTC_Date >= StartSummerTime and UTC_Date < StartWinterTime then 2 else 1,
      CET_Timezone     = DateTimeZone.SwitchZone(UTC_DateTimeZone, UTC_Offset)

    If you like you can find an elaboration on my blog:


    • Unfortunately this isn’t a 100% reliable solution: you have hard coded the dates when daylight savings starts and ends (it can vary from year to year and country to country, and some countries don’t always have daylight savings time), and it doesn’t account for historic variations in offsets.

Leave a Reply to Gordon Parmenter Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: