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

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.

Highlighted
New Member

Re: time

thx the strip of "z" worked!

Highlighted
New Member

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?

Highlighted
Advocate I
Advocate I

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

Highlighted
Advocate I
Advocate I

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
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published 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 (6,530)