cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
psbees
Level: Power Up

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
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: time

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.

psbees
Level: Power Up

Re: time

thx the strip of "z" worked!

Choksbergen
Level: Power Up

Re: time

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?

jrowe
Level: Powered On

Re: time

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 +/-.

jrowe
Level: Powered On

Re: time

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
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 267 members 4,221 guests
Please welcome our newest community members: