web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : o8q/f1D+yJyNmTuh/xDqZO
Power Apps - Building Power Apps
Unanswered

Date from excel table is one day earlier

Like (0) ShareShare
ReportReport
Posted on 15 Feb 2017 15:17:57 by

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!

Categories:
  • Community Power Platform Member Profile Picture
    on 06 Apr 2019 at 15:33:27
    Re: Date from excel table is one day earlier
    I believe the problem is this. It occurs when trying to import Microsoft’s 1900 date format (days since 1 Jan 1900) in a software that doesn’t compensate for the error Microsoft made in counting the number of days since 1 Jan 1900. Microsoft included a leap year day for the year 1900 in that format when they shouldn’t have (it was not a leap year). There was a Microsoft support document that openly admitted the error and their reason for leaving it in place—basically for a whole bunch of compatibility reasons with things like Lotus at the time—but I can no longer find the document.

    When I first saw this error importing data into a non-Microsoft software (but it can also occur in the reverse), I too was fumbling around for ages trying to diagnose it. Microsoft even made a 1904 date format especially for the Mac to get around this and other issues on the Mac platform. It’s why you’ll see a similar issue of dates being off by four years and one day on the Mac, not four years exactly, as the format would otherwise suggest. It’s can be especially problematic and hard to diagnose in date calculations that span the period in question.

    For this reason I always store dates as separate integers (year, month, day) or as a single integer in the YYYYMMDD format if absolutely necessary. This is best practice to avoid conversion issues, as anyone who’s done data QA/QC can attest. You still have to be careful doing any calculations that the two (or more) sources are correct and consistent and that any formatting issues have been sorted.
  • Community Power Platform Member Profile Picture
    on 04 Sep 2018 at 03:42:44
    Re: Date from excel table is one day earlier

    This is a workaround and not a perfect solution.

     

    My case: I have a powerapp that reads and writes date into an excel. The data from the excel is used by other services to create charts and to do analysis. Users are in Australia and India. Anytime someone adds a date, the same is saved as Date -1 in excel.

     

    Workaround - When I added a date, it was input as Date 12AM, Considering I am in India, the date was saved as Date-1 1830. So instead of sending date as Date 12AM, I sent it as Date 11AM (considering max(530 hrs for India and 11 hrs for Australia. You can also set one or two hours later just in case). I didnot delete the Hrs and Mins drop down in EDIT screen instead I hid them (set visible to NO) and set the default of 'hours' field to 11. This way when the system writes the date, it does it as Date 12AM (UTC conversion).

     

    Since time was not very important for my data analysis this solution works for me. Hope it helps

     

  • Meneghino Profile Picture
    6,949 on 23 Feb 2017 at 14:25:16
    Re: Date from excel table is one day earlier

    Hi @Anonymous

    I would have yyyy-mm-dd text in Excel as well.  You just patch the Text(Data; "yyyy-mm-dd")

  • Community Power Platform Member Profile Picture
    on 23 Feb 2017 at 14:12:27
    Re: Date from excel table is one day earlier

    @Meneghino Wait, so it would be like this?

    the Data column on excel should be in text format, dd/mm/yyyy

    On power apps, < Text(Data; "[$-pt-BR]yyyy/mm/dd"; "pt-BR")  > ????

    Tried this and Im having the same problem with the ordering part, it orders just by day, not my month or year(Only on equals day numbers, then it goes to order my month

  • Meneghino Profile Picture
    6,949 on 23 Feb 2017 at 13:52:48
    Re: Date from excel table is one day earlier

    Hi @Anonymous

    If the text is "yyyy-mm-dd" then the ordering will work, that is why I normally use this format.

  • Community Power Platform Member Profile Picture
    on 23 Feb 2017 at 13:22:04
    Re: Date from excel table is one day earlier

    @Meneghino The problem on saving it as a text is that I wont be able to order it as date anymore, I have a button on the list that orders it by date, if its on text format, The days will be in order even if the months are different!

  • Meneghino Profile Picture
    6,949 on 23 Feb 2017 at 12:55:36
    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.

  • Community Power Platform Member Profile Picture
    on 23 Feb 2017 at 12:51:14
    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?

  • AndyPennell Profile Picture
    on 22 Feb 2017 at 22:46:16
    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.

  • AndyPennell Profile Picture
    on 22 Feb 2017 at 20:48:41
    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).

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 791 Most Valuable Professional

#2
MS.Ragavendar Profile Picture

MS.Ragavendar 410 Super User 2025 Season 2

#3
mmbr1606 Profile Picture

mmbr1606 275 Super User 2025 Season 2

Loading complete