cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Date from excel table is one day earlier

Hi,

On my current app, i have dates on an excel sheet, they are beign shown on a gallery, the problem is the date that shows up is always one day before the actual date( If i type 31/12 on excel, it will show up as 30/12 in the app)(pt-br date format)

 

Any ideas why this is happening? 

Thanks!

15 REPLIES 15
Community Support Team
Community Support Team

Re: Date from excel table is one day earlier

Hi PeFischer,

 

I tried to create an app based on Excel. And the date show properly on the app, equal to the actual date.
2.PNG

3.PNG

 

Is the time zone of PowerApps the same with the local time zone of your PC? Please try to format them to the same to see if it works.

 

Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Date from excel table is one day earlier

@v-yamao-msft

Hi, thanks for your reply!

So, the date on power apps is currently like this 

Text(Data; "[$-pt-BR]dd/mm/yyyy")

So its on the same format as my pc right? I did not find how to set the exact timezone as my pc(Brazil has like 3 different time zones).

Even with this format on the code, it stills shows up one day before the day that I want.

Community Support Team
Community Support Team

Re: Date from excel table is one day earlier

Hi PeFischer,

 

For testing, first, please try to format the local time zone of your PC to one of them(Brazil has like 3 different time zones).


Second, please try to change the time zone setting in Microsoft Office 365 to the one you have set on your local PC, this document could be a reference for you:
https://uiutechsupport.sherpadesk.com/article/4kofhw/changing-the-time-zone-setting-in-microsoft-off...


Third, please create an app based on the Excel table to see if the date on the app is equal to the actual date on Excel.

 

If you are still getting the issue, please check this similar thread for a reference, it seems that there is an issue with Date (not DateTime) when serialized to/from some data sources:
https://powerusers.microsoft.com/t5/PowerApps-Forum/Please-help-with-UTC/m-p/21244#M9183

 

Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
LarsDP
Level 8

Re: Date from excel table is one day earlier

Adding 1 hour to the date should solve this

Meneghino
Level 10

Re: Date from excel table is one day earlier

I think the date is probably saved to Excel as UTC.

As @v-yamao-msft suggests, please see this

PowerApps Staff AndyPennell
PowerApps Staff

Re: Date from excel table is one day earlier

Excel is, um, surprising, when it comes to date/times: they are saved without any timezone data at all.

 

Also a formula like Text(Data; "[$-pt-BR]dd/mm/yyyy") says "parse the date in Portuguese", but "display it in the local format". If you want Portugues output everywhere, try

 

Text(Data; "[$-pt-BR]dd/mm/yyyy"; "pt-BR")

Yes, there are two locales here: the input locale (used to parse the date string) and the output locale (used to display the result).

PowerApps Staff AndyPennell
PowerApps Staff

Re: Date from excel table is one day earlier

On read-reading the OP, is this Excel loaded via static import, or reading an Excel file via a Connector? Excel static import should always treat datetime as the local timezone, but the Connectors version MAY be treating it as the timezone of wherever the servers are located.

 

I know a bunch about Excel static import, but nothing about how the Connectors work, so the latter is just a guess.

Anonymous
Not applicable

Re: Date from excel table is one day earlier

Tried all of your suggestions guys, none of them worked =(

I need to have this because i'm not going to be the one updating the info on the app, so I cant rely on "Just add one date on the sheet"

As for what @AndyPennell asked, i guess its connector, as I'm using a table that's on my OneDrive Business acc, Mayba the timezone of the servers are another one, since its a multinational organization. But there's no possible way to change it only on my side? 

 

Any other sugestions?

Meneghino
Level 10

Re: Date from excel table is one day earlier

Hi @Anonymous

Can you save the date as text? "yyyy-mm-dd"

This way you can then parse it in your application or in Excel if you need 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 (4,565)