cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ptcsoderlund
Level: Powered On

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
Meneghino
Level 10

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
Meneghino
Level 10

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

ptcsoderlund
Level: Powered On

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.

ptcsoderlund
Level: Powered On

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.

afernando
Level 10

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. 

ptcsoderlund
Level: Powered On

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.

Super User
Super User

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.

ptcsoderlund
Level: Powered On

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.

bramsey71
Level: Powered On

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.

ptcsoderlund
Level: Powered On

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
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 (Last 30 Days)
Users online (7,001)