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

Highlighted
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
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 324 members 5,556 guests
Please welcome our newest community members: