cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Helper I
Helper I

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

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.
Highlighted
Helper I
Helper I

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 ?

Highlighted
Dual Super User II
Dual Super User II

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.
Highlighted
Helper I
Helper I

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
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (3,746)