Answered

Time zone issue

Like (0) Share

Report

Posted on 21 Jan 2020 11:03:40 by alexlrca

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.

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:

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):

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):

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.

Categories:

Connecting to Data

General Questions

All responses (4)

Answers (1)

Verified answer

alexlrca 58 on 22 Jan 2020 at 12:59:23

Like (0)

Report

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.
Pstork1 67,351 Most Valuable Professional on 22 Jan 2020 at 12:45:46

Like (0)

Report

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.
alexlrca 58 on 21 Jan 2020 at 13:41:37

Like (0)

Report

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 ?
Pstork1 67,351 Most Valuable Professional on 21 Jan 2020 at 13:24:00

Like (0)

Report

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/