Forum Discussion
Helper IConvert Date/Time from UTC to Local Time When Data is in 2 tables
Hi,
I am working on an office capacity report. This will have lots of screenshots. I have multiple data tables in Desktop:
- fromO365Logs: this shows the user, the IP address that they logged into on a given day/time
- OfficeIP: this shows the IP address of each physical office location
- OfficeDetails: this shows the details for the location of each physical location. This is linked to the OfficeIP dataset via a Many-to-One relationship. It is also linked to the fromO365Logs dataset, also via a Many-to-One relationship.
- TimeData: this shows the employee, the date that they submitted hours, and the type of hours they submitted (i.e. regular, OT, vacation, etc. I do not know if this table is relevant to my issue so am including in case
The issue is that employees in our locations globally are showing up on the wrong date due to us using UTC. As an example, anyone who worked Monday, November 1 in Australia is showing up in our data charts as having worked on Sunday, October 31. Example: this employee signed in on 10/31 at 10:08 pm UTC, which is actually 11/1 at 8:08 AM local time:
Is there any way, or any formula or function, that we can use to convert the date/time from the fromO365Logs dataset based on the Country field in Office Details (using the linked OfficeIP/Office Details)? I would, somehow, have to be able to look at the Country and the date/time and then use the formula to transfer from UTC to local time.
Is this possible?
Thanks.
Hi dcohare ,
The function DateTime.AddZone() which I thought was used to convert between timezones is actually used to add timezone information to an existing date. If your date is UTC you would use DateTime.AddZone([date_created],0) and if your date was already in AEST then you would use DateTime.AddZone([date_created],10).
such as:
DateTimeZone.ToLocal(DateTime.AddZone([date_created_UTC],0))Power Query will correctly convert my date created from UTC to AEST (or whatever is local).
For more details, you can read related blog and video about Convert UTC datetime to local time zones
How-To: Convert UTC to Your Local Time Zone in Pow... - Microsoft Power BI Community
Convert UTC datetime to local time zones in Power Query - YouTube
If the problem is still not resolved, please point it out. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
v-henryk-mstfCommunity Support
Hi dcohare ,
The function DateTime.AddZone() which I thought was used to convert between timezones is actually used to add timezone information to an existing date. If your date is UTC you would use DateTime.AddZone([date_created],0) and if your date was already in AEST then you would use DateTime.AddZone([date_created],10).
such as:
DateTimeZone.ToLocal(DateTime.AddZone([date_created_UTC],0))Power Query will correctly convert my date created from UTC to AEST (or whatever is local).
For more details, you can read related blog and video about Convert UTC datetime to local time zones
How-To: Convert UTC to Your Local Time Zone in Pow... - Microsoft Power BI Community
Convert UTC datetime to local time zones in Power Query - YouTube
If the problem is still not resolved, please point it out. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.