cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

PowerApps sharepointlist Timezone

I have set up a sharepoint list with a date column, If I add a record on the date 2017-jan-27 for example.

My powerapps app shows the date as 2017-jan-26. If I change timezone in powerapps to utc instead of local it shows the date 2017-dec-25.

What is up with that, I get it that they handle timezones differently (sharepoint vs powerapps). But this can´t be right.

What am I missing?

 

The sharepoint list is set to Timezone stockholm (utc + 01), Powerapps are set to local and that should also be utc + 01 on every client (so far).

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: PowerApps sharepointlist Timezone

Hi @ptcsoderlund

I agree that the way timezones are handled by PowerApps is really confusing, but I think timezones in databases are really confusing anyway.  I have tried a couple of times to figure it out but gave up.  There is no real proper documentation on this.

What I do for now is to use an integer column instead to store my date.  I store the date as the integer of yyyymmdd, so today would be 20170123.  This has the advantages that:

  • the date is the same however you access the database
  • there is no hidden time portion
  • it is easily parsable (i.e. converted to a date, and a date is easily converted to this integer)
  • you can sort this column in date order just like a date
  • the column can be filtered just like dates

If you still need to show a date column in SharePoint you can add a calculated column to your SharePoint list to be

=DATE(ROUNDDOWN(MyDateInteger/10000,0),ROUNDDOWN(MyDateInteger/100,0)-ROUNDDOWN(MyDateInteger/10000,0)*100,MyDateInteger-ROUNDDOWN(MyDateInteger/100,0)*100)

Please let me know what you think

View solution in original post

10 REPLIES 10
Highlighted
Community Champion
Community Champion

Re: PowerApps sharepointlist Timezone

Hi @ptcsoderlund

I agree that the way timezones are handled by PowerApps is really confusing, but I think timezones in databases are really confusing anyway.  I have tried a couple of times to figure it out but gave up.  There is no real proper documentation on this.

What I do for now is to use an integer column instead to store my date.  I store the date as the integer of yyyymmdd, so today would be 20170123.  This has the advantages that:

  • the date is the same however you access the database
  • there is no hidden time portion
  • it is easily parsable (i.e. converted to a date, and a date is easily converted to this integer)
  • you can sort this column in date order just like a date
  • the column can be filtered just like dates

If you still need to show a date column in SharePoint you can add a calculated column to your SharePoint list to be

=DATE(ROUNDDOWN(MyDateInteger/10000,0),ROUNDDOWN(MyDateInteger/100,0)-ROUNDDOWN(MyDateInteger/10000,0)*100,MyDateInteger-ROUNDDOWN(MyDateInteger/100,0)*100)

Please let me know what you think

View solution in original post

Highlighted
Regular Visitor

Re: PowerApps sharepointlist Timezone

Good idea, I will probably use it if I write applications that feed data.

My end users want to have a date format so I have to leave it as date format.

 

I am currently using Date +1 in powerapps, which will stop working when the client localtime is not utc + 01.

I just find it weird that timezone can get date 2 days wrong from sharepoint to powerapps.

 

Seems to me that the only solution is to use UTC 00 on both places to get correct date.

Highlighted
Regular Visitor

Re: PowerApps sharepointlist Timezone

I´m not a fan of accepting workarounds as a solution. I think the problem should be fixed by the powerapps/sharepoint dev teams.

But this is the best help I/one can get.

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: PowerApps sharepointlist Timezone

I'm also having the same issue and unfortunately, the workaround mentioned here isn't going to help me because I connect to my database using directquery in Power BI. And it doesn't allow changing data types. (int to date).

 

This is really frustrating. 

Highlighted
Regular Visitor

Re: PowerApps sharepointlist Timezone

Agree, frustrating.

You can´t depend on powerapps as it is now, then you would have to design database and systems based on powerapps.

When you really should design powerapps after database and systems.

Highlighted
Resident Rockstar
Resident Rockstar

Re: PowerApps sharepointlist Timezone

Nothing new on this in over a year.  I'm having this issue.  I dont want work-arounds because I also have date+time fields, and daylight savings times are an issue.  I have all of my dates stored in SP as utc() from flow.  I thought that to be the most "pure" format.  When you look at a SP list, it all looks good.  Dates convert.  When I use datacards, dates also seem to be good in PA.  However, when using labels or ThisItem.DateVariable then things get screwy.  I will probably post a new issue on this.

Highlighted
Regular Visitor

Re: PowerApps sharepointlist Timezone

You should, but dont expect things to change.

Power BI also suffers from this.

 

In PBI i am inserting data at utc, when viewing in live tiles date gets converted to localutc. But when creating reports time is in utc without any option to set local utc.

It seems to me that all the "Power" products share the same engine and problems.

 

I thought this would be resolved any time soon now since the services are worldwide.

 

 

I think you are right, dates should always be stored as utc and converted when viewing.

Highlighted
Anonymous
Not applicable

Re: PowerApps sharepointlist Timezone

I ran into the same issue.  My workaround was to set the DateTimeZone to property to UTC and then I am manually adjusting the time myself using the DateAdd function to add 6 hours to the value.

 

On the DataCard I added the DateAdd to the Update property and on the date field I added it to the DefaultDate property.  Working good so far but definitely a pain to have to add it.

Highlighted
Regular Visitor

Re: PowerApps sharepointlist Timezone

Sweden have UTC +2 during summer and UTC +1 during winter.

I'm not ready to try and do any workarounds in order for it to work.

I´ll just stay away from powerapps for now.

 

Havent used powerapps in a while now, but it seems like it has not been fixed yet.

 

Edit: After a quick look it seems like you can choose timezone (local or UTC) now in datepicker.

Which should make everything work now. I havent tested it.

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (11,429)