UTC to AEST (Australian Eastern Standard Time) with Daylight Saving

[jjmanx]

Hey all, this isn't actually a question but something I created which may help others that find themselves in a similar situation. 

Below is code to create a custom column with the number of hours needed to switch a UTC column to Aus EST, taking into account daylight savings. 

Steps:

1. Ensure your UTC column is set to type = 'Date/Time/Zone'
2. Sort your UTC column to ascending
3. Create a custom column using the code below (replacing my [orderDate] with your UTC column name)
4. 'Fill Down' on your new custom column
5. Change your custom column to 'Whole Number'
6. Create a new custom column to add timezone hours to your original UTC column
   i.e. 'DateTimeZone.SwitchZone([orderDate], [your custom column])
7. Change type of new column to 'Date/Time/Zone'
8. Done! 

Hopefully this helps. I've only run this on a smallish dataset (18 months of data) so I've not done a huge amount of testing but I've not encountered any issues as of yet. However, open to improvement suggestions!

Thanks,

John 

if Date.Month(DateTimeZone.SwitchZone([orderDate], 10)) = 4 and
(Date.DayOfWeekName(DateTimeZone.SwitchZone([orderDate], 10)) = "Sunday" and Date.Day(DateTimeZone.SwitchZone([orderDate], 10)) <= 7 and Date.Day(DateTimeZone.SwitchZone([orderDate], 10)) > 1 
or Date.DayOfWeekName(DateTimeZone.SwitchZone([orderDate], 10)) = "Sunday" and Date.Day(DateTimeZone.SwitchZone([orderDate], 10)) = 1 and DateTime.Time(DateTimeZone.SwitchZone([orderDate], 10)) > #time(3,0,0)
or Date.Day(DateTimeZone.SwitchZone([orderDate], 10)) >= 7
or Date.DayOfWeekName(DateTimeZone.SwitchZone([orderDate], 10)) = "Monday" and List.Contains({2, 3, 4, 5, 6}, Date.Day(DateTimeZone.SwitchZone([orderDate], 10)))
or Date.DayOfWeekName(DateTimeZone.SwitchZone([orderDate], 10)) = "Tuesday" and List.Contains({3, 4, 5, 6}, Date.Day(DateTimeZone.SwitchZone([orderDate], 10)))
or Date.DayOfWeekName(DateTimeZone.SwitchZone([orderDate], 10)) = "Wednesday" and List.Contains({4, 5, 6}, Date.Day(DateTimeZone.SwitchZone([orderDate], 10)))
or Date.DayOfWeekName(DateTimeZone.SwitchZone([orderDate], 10)) = "Thursday" and List.Contains({5, 6}, Date.Day(DateTimeZone.SwitchZone([orderDate], 10)))
or Date.DayOfWeekName(DateTimeZone.SwitchZone([orderDate], 10)) = "Friday" and Date.Day(DateTimeZone.SwitchZone([orderDate], 10)) = 6
or Date.Day(DateTimeZone.SwitchZone([orderDate], 10)) >= 7) then 10 
else if Date.Month(DateTimeZone.SwitchZone([orderDate], 10)) = 10 and
(Date.DayOfWeekName(DateTimeZone.SwitchZone([orderDate], 10)) = "Sunday" and Date.Day(DateTimeZone.SwitchZone([orderDate], 10)) <= 7 and Date.Day(DateTimeZone.SwitchZone([orderDate], 10)) > 1 
or Date.DayOfWeekName(DateTimeZone.SwitchZone([orderDate], 10)) = "Sunday" and Date.Day(DateTimeZone.SwitchZone([orderDate], 10)) = 1 and DateTime.Time(DateTimeZone.SwitchZone([orderDate], 10)) > #time(3,0,0)
or Date.Day(DateTimeZone.SwitchZone([orderDate], 10)) >= 7
or Date.DayOfWeekName(DateTimeZone.SwitchZone([orderDate], 10)) = "Monday" and List.Contains({2, 3, 4, 5, 6}, Date.Day(DateTimeZone.SwitchZone([orderDate], 10)))
or Date.DayOfWeekName(DateTimeZone.SwitchZone([orderDate], 10)) = "Tuesday" and List.Contains({3, 4, 5, 6}, Date.Day(DateTimeZone.SwitchZone([orderDate], 10)))
or Date.DayOfWeekName(DateTimeZone.SwitchZone([orderDate], 10)) = "Wednesday" and List.Contains({4, 5, 6}, Date.Day(DateTimeZone.SwitchZone([orderDate], 10)))
or Date.DayOfWeekName(DateTimeZone.SwitchZone([orderDate], 10)) = "Thursday" and List.Contains({5, 6}, Date.Day(DateTimeZone.SwitchZone([orderDate], 10)))
or Date.DayOfWeekName(DateTimeZone.SwitchZone([orderDate], 10)) = "Friday" and Date.Day(DateTimeZone.SwitchZone([orderDate], 10)) = 6
or Date.Day(DateTimeZone.SwitchZone([orderDate], 10)) >= 7)
then 11
else null

[jjmanx · Accepted solution]

In short,

- it repeatedly adds 10 hours to the UTC time (which is the minimum number of hours NSW is ahead of UTC)

- then checks whether the new date is in April or October and whether the date is either the 1st Sunday in the month (and what time) or a day after
- it then returns 10 (April) or 11 (October) to be used to convert the UTC time which you then use to update the original UTC