I'm new to powerapps, and I created an app using a simple excel table with:
Time | Event| Group as my columns
The time in my excel file is formatted as h:hh a.m.
the time showing up on my powerapp is zulu time 8 hrs difference.
How do I correct this in my app?
Currently we don't have any easy way to convert between local time (what you want) and UTC (Zulu time). We're working to make the experience easier, but in the interim you can use the workaround shown below.
Basically, times in excel are stored in the local time zone, and sent as strings to PowerApps. You can use the DateTmeValue function to convert between the string representation (in UTC) and a time value, and then the Text function to display it in a prettier format:
One thing that I've noticed, however, is that times in Excel are always stored in UTC, so if you try to convert it to string with the functions shown above, you will have the time in your time zone, which will likely be different than the value in excel. One solution for this is to strip the 'Z' from the time string representation prior to converting it to a DateTime value, and that will make PowerApps interpret the value as a local time:
Text(DateTimeValue(Substitute(ThisItem.Time, "Z", "")), DateTimeFormat.ShortTime)
Hope this helps.
I'm running into the same type of an issue. I'm reading in data from a database, and using the date/time result to decide what to display. Unfortunately, it seems that PowerApps automatically adjusts my time as if the time it read from the SQL Server was in UTC (it's not), changing it five hours.
I'm trying to use the date (COMPLETE_DT) in a formula, as follows.
Filter('[dbo].[RUN]', DatePicker1.SelectedDate < COMPLETE_DT && DatePicker1.SelectedDate > DateAdd(COMPLETE_DT, -1, Days))
Obviously I can't use the text() solution that works in display situations because in order to compare DatePicker1.SelectedDate and COMPLETE_CT, the latter MUST be a date/time field. Unfortunately, DateAdd wont let me add in hour increments, so I can't figure out a way to deal with the five hour time difference than I'm seeing. Any suggestions?
SQL datetime column: 3/24/2017 2:37:00 PM (local time not UTC)
powerapps display: 3/24/2017 10:37:00 AM (powerapps converts it but shouldn't)
I am in the EST timezone so right now our offset is UTC - 4, when it changes we are UTC - 5. I figured out a way arounf the DST change and to make powerapps display correctly. The following formula will give you your offset.
Value( Hour(DateTimeValue(Substitute(Text( Now(),DateTimeFormat.UTC ), "Z", "")))) - Value( Hour(DateTimeValue(Text( Now(),DateTimeFormat.UTC ))))
Depending on how your data is being displayed you could do the following.
EX 1: ThisItem.date becomes ThisItem.date + (Value( Hour(DateTimeValue(Substitute(Text( Now(),DateTimeFormat.UTC ), "Z", "")))) - Value( Hour(DateTimeValue(Text( Now(),DateTimeFormat.UTC )))))/24
EX 2: Parent.Default becomes DateTimeValue(Parent.Default) + (Value( Hour(DateTimeValue(Substitute(Text( Now(),DateTimeFormat.UTC ), "Z", "")))) - Value( Hour(DateTimeValue(Text( Now(),DateTimeFormat.UTC )))))/24
Essentially what I am doing is finding my UTC offset and adding it back to the time displayed by Powerapps.
Note: This works for a timezone less then UTC, if your timezone is lets say,UTC + 5 then you might have to change the order of the formula or +/-.
After looking at it a bit more I determined that the previous post will have problems around midnight. I was trying to do a one-line fix but unfortunately powerapps doesn't have the functionality to do that without that line becoming majorly huge.
To solve this I made 3 hidden text boxes.
Textbox1: Value( Hour(DateTimeValue(Substitute(Text( Now(),DateTimeFormat.UTC ), "Z", ""))))
Textbox2: Value( Hour(DateTimeValue(Text( Now(),DateTimeFormat.UTC ))))
Textbox3: If(Value(TextBox2.Text) > Value(TextBox3.Text), Value(TextBox2.Text) - Value(TextBox3.Text), Value(TextBox2.Text) + 24 - Value(TextBox3.Text))
Then you would do
DateTimeValue(Parent.Default) + Value(TextBox3.Text)/24
Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.
Fill out a quick form to claim your user group badge now!
Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities
Features releasing from October 2019 through March 2020
Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.
Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications