Convert Date/Time in UTC to Local Time with Daylight savings

I really appreciate you sharing your code with all of us.  This UTC/daylight savings conversion is rough!

In this new segment...

     timeZone = 10 + timeZoneAdj,

 ... is where you have "10" supposed to be where we state our own offset to UTC?  For example, I'm in the US Eastern time (EDT right now), so do I change that to 4?... or 5 and the "isSummerTime" part handles when to change it to 4 during our summer time?  

Thanks in advance!

EDIT:  Figured it out!  Had to use timeZone = (-5 + timeZoneAdj), and that worked.  Verified it's properly converting my historical date column either 4 or 5 hours, depending on whether we're in Daylight Savings Time or not for each date in the column.  Works like a charm, thanks!

You are champion.  Thanks for your great help !  I will test thoroughly.  Wish I have time to learn M language.

Very glad to help.  I am better at tweaking others code, than to create myself.  Please use the sample code that I provided above to analyze the periods, as it focuses on the dates around DST only.  That will make it easier to discover issues with the timing, if there is any.

Sure, it is not actually my code, I only tweaked it from a post above.

What is your datasource for your data?  Honestly, if anyone is using SQL Server 2016 or greater, changing the line in your SQL I feel is much more efficient.

Something like:

[startDate] AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS [startDate]

This can be done for other timezones also, so please refer to https://bertwagner.com/posts/at-time-zone-the-easy-way-to-deal-with-time-zones-and-daylight-savings-time/.

Okay, so I looked over the code, and I think I have made the tweaks necessary for jtao for the correct DST for AST:

(datetimecolumn as nullable datetime) =>

let
date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),

// The offset from UTC to your Timezone
// ex. AEST is 11 hours later than UTC, so it would be +11
// ex. EST is 5 hours earlier than UTC, so it would be -5
UTCOffset = 11,

firstSundayOfOctober = DateTime.From(Date.StartOfWeek(#date(Date.Year(date), 10, 7), Day.Sunday)) + #duration(0, -UTCOffset+1, 0, 0),
firstSundayOfApril = DateTime.From(Date.StartOfWeek(#date(Date.Year(date), 4, 7), Day.Sunday)) + #duration(0, -UTCOffset+2, 0, 0),

isDaylightSavingsTime =	
		datetimecolumn >= firstSundayOfApril
		and
		datetimecolumn < firstSundayOfOctober,

AdjustForDST = if datetimecolumn is null then 0 else Number.From(isDaylightSavingsTime),
timeZone = UTCOffset + AdjustForDST,

AEST = 
        DateTime.From(datetimecolumn) + #duration(0, timeZone, 0, 0)
in
    AEST

I am also in EST, and I tweaked and tested the logic which works.  Be aware that we changed DST on the second Sunday of March, and the first Sunday of November.

(datetimecolumn as nullable datetime) =>

let
date = DateTime.Date(datetimecolumn),

// The offset from UTC to your Timezone
// ex. AEST is 11 hours later than UTC, so it would be +11
// ex. EST is 5 hours earlier than UTC, so it would be -5
UTCOffset = -5,

firstSundayOfNovember = DateTime.From(Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday)) + #duration(0, -UTCOffset+1, 0, 0),
secondSundayOfMarch = DateTime.From(Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday)) + #duration(0, -UTCOffset+2, 0, 0),

isDaylightSavingsTime =	
                datetimecolumn >= secondSundayOfMarch
                and
                datetimecolumn < firstSundayOfNovember,

AdjustForDST = if datetimecolumn is null then 0 else Number.From(isDaylightSavingsTime),
timeZone = UTCOffset + AdjustForDST,

EST = 
        DateTime.From(datetimecolumn) + #duration(0, timeZone, 0, 0)
in
    EST

I realize my test script above was bad, and I below I have pasted a new one that correctly shows the dates with 1 hour increments which will correctly allow you to see if things are changing as expected.

First make sure you add the Local_To_UTC function

let
    Source = (datetimecolumn as nullable datetime) =>

    let

        DateTimeAddZone = DateTime.AddZone( datetimecolumn, 0 ),
        DateTimetoLocal = DateTimeZone.ToLocal( DateTimeAddZone ),
        DateTimeRemoveZone = DateTimeZone.RemoveZone( DateTimetoLocal ),

        UTC_To_Local = DateTimeRemoveZone

    in
        UTC_To_Local
in
    Source

Since they are different functions and date periods for AST and EST, you will need to use the corresponding test script.

For testing the AST, use:

let
    StartDate = #date(2019, 1, 1),
    EndDate = #date(2021, 12, 31),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source =  List.DateTimes(DateTime.From(StartDate),DayCount*24,#duration(0,1,0,0)), 
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),    
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(ChangedType,{{"Column1", "DateUTC"}}), 
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "DayOfWeekName", each Date.DayOfWeekName( [DateUTC] )),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "FilterDates", each if ( DateTime.Date( [DateUTC] ) >= #date( Date.Year( DateTime.Date ( [DateUTC] )), 3, 31 ) and DateTime.Date( [DateUTC] ) <= #date( Date.Year( DateTime.Date ( [DateUTC] )), 4, 8 ))  or
                                                                                ( DateTime.Date( [DateUTC] ) >= #date( Date.Year( DateTime.Date ( [DateUTC] )), 9, 30 ) and DateTime.Date( [DateUTC] ) <= #date( Date.Year( DateTime.Date ( [DateUTC] )), 10, 8 )) then "Y" else "N"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([FilterDates] = "Y") and ([DayOfWeekName] = "Saturday" or [DayOfWeekName] = "Sunday")),
    #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "DateAST", each AST([DateUTC]), type datetime),
    #"Invoked Custom Function1" = Table.AddColumn(#"Invoked Custom Function", "UTC_to_Local", each UTC_to_Local([DateUTC]), type datetime)
in
    #"Invoked Custom Function1"

For testing the EST, use the same script as above, though change the section from:

#"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "DateAST", each AST([DateUTC]), type datetime),

to be:

#"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "EST", each EST([DateUTC]), type datetime),

Okay, need to get back to work.  I have projects with deadlines, and I got carried away working on this.  Hope this is helpful to you both, and perhaps others.