cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Community Support

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

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 Kudo Commander
Kudo Commander

Re: Date from excel table is one day earlier

Adding 1 hour to the date should solve this

Meneghino Community Champion
Community Champion

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

Power Apps
Power Apps

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).

Power Apps
Power Apps

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?

Highlighted
Meneghino Community Champion
Community Champion

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
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (7,683)