cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
alexlrca
Level: Powered On

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
Level: Powered On

Re: Time zone issue

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
Dual Super User
Dual Super User

Re: Time zone issue

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.
alexlrca
Level: Powered On

Re: Time zone issue

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 ?

Dual Super User
Dual Super User

Re: Time zone issue

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
Level: Powered On

Re: Time zone issue

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
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (4,690)