cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alexlrca
Helper I
Helper I

Time zone issue

I have a Form with a DataCard connected to a Excel file stored on OneDrive with a datetime column and ID column. I was having some problems with the date/time stored in the Excel, and I thought it was related to time zones. I am from Brussels (in UTC+1). Here's the Form that creates the data in the Excel file. The PlaceHolder field is just an ID to be able to retrieve the datetime in an EditForm. The Update property of the DataCard is shown below.  OnSelect of the Create Entry button only Submits the Form. Parameters of the DatePicker are also shown below.

 

2020-01-21_11-48-46.png2020-01-21_11-51-55.png2020-01-21_11-54-41.png

 

 

The problem is the following. When I pick a Date in the DatePicker and Time in the Dropdowns, and that I submit the form, the data saved in Excel is always - 1 hour than the one I selected. For example: I select 01/30/2020 20:00, submit the Form, and here is the data saved in the Excel file:

 

2020-01-21_11-58-08.png

 

I checked the time zone of my account in Outlook Online, and it is in UTC+1, which is my current time zone (see below): 

 

2020-01-21_11-59-44.png

 

I checked the time zone of my account in SharePoint, and it is in UTC+1, which is also my current time zone (see below): 

 

2020-01-21_12-01-45.png

 

I tried to open an Excel file with Excel Online and use the Now() function to check on which Time Zone it was set, and the time is correctly displayed in local time (UTC+1).

 

I tried to switch the DateTimeZone property of the DatePicker from Local to UTC. The results are the following: the time saved in the Excel file is the right one ( = the one I entered in PowerApps). However, when I retrieve it in PowerApps, it adds 1 hour to it. The property of the DateTime DataCard in my EditForm are the same than in the Create Form.

1 ACCEPTED SOLUTION

Accepted Solutions
alexlrca
Helper I
Helper I

The problem was fixed by switching the user's time zone from UTC+1 (Local Time) to UTC in their smartphones' settings.

Therefore, when they send the data from PowerApps to Excel, the dates are already sent in UTC.

Changing the time zones in the Office settings (Outlook or Sharepoint) did not solve the problem. 

View solution in original post

4 REPLIES 4
Pstork1
Dual Super User III
Dual Super User III

The problem is that Excel stores time as UTC.  So the time itself is correct, but the display is incorrect.  Take a look at the following article for a fuller explanation of what is happening.

https://powerapps.microsoft.com/en-us/blog/working-with-datetime-values-in-sql/



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Considering that Excel stores time in UTC..

If I switch my Office time zone from UTC+1 to UTC, as well as the DateTimeZone ofthe DatePicker in my DataCard from "Local" to "UTC".. Will it solve the problem ?

Pstork1
Dual Super User III
Dual Super User III

It may.  But if other connectors are involved, like SharePoint or Outlook, you may still have problems when you try to use the date/time that is stored in Excel in those environments.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
alexlrca
Helper I
Helper I

The problem was fixed by switching the user's time zone from UTC+1 (Local Time) to UTC in their smartphones' settings.

Therefore, when they send the data from PowerApps to Excel, the dates are already sent in UTC.

Changing the time zones in the Office settings (Outlook or Sharepoint) did not solve the problem. 

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,065)