cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

time

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?

 

5 REPLIES 5
Power Apps
Power Apps

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:

Text(DateTimeValue(ThisItem.Time), DateTimeFormat.ShortTime)

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.

thx the strip of "z" worked!

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

Helpful resources

Announcements
secondImage

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.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Solution Authors
Top Kudoed Authors
Users online (28,962)