I must be doing something stupidly dumb here, and I am hoping someone can nudme me in the right direction.
I have an Excel file with a table that includes a date field. In my "edit" form, I am using a DatePicker control wired to the "Date" field in the table. I can input a new record or update an existing one with no problems and the selected date gets into the table correctly. The problem is that when I view the records in a Gallery or otherwise pull the record up later, the date is early by one day.
If I display the date in a text control, it is formatted like this: "2016-05-12T00:00:00Z". The DatePicker control will, in this example, be displaying "5/11/2016". The record in the Excel table is "5/12/2016". I suspect the issue is with a time zone conversion. In fact, if I manually change the record in the Excel table to "5/12/2016 08:30", that record then displays as expected. I am in the US central time zone.
Has anyone encountered this before? If so, did you engineer any kind of "solution"?
I am probably going to whip up a formula to add the hour and minute to the field using Now() and Text(), but would love to hear anyone's thoughts.
Note- recording the time is not really essential for my purposes, but it wouldn't hurt to have it. I am also thinking of adding drop down controls for selecting the time (this is meant to be a mobile app, so trying to avoid a lot of text entry).
Solved! Go to Solution.
Here's a very simple way to solve the issue.
In this example, DatePickerControl is the name of a Date Picker control.
Set(DateSelected, DateAdd( DatePickerControl.SelectedDate, TimeZoneOffset(DatePickerControl.SelectedDate), Minutes ));
I have a form app that i am having issues with the time displayed, wondering if anyone could help. Basically it has an edit screen with a date selector that populates an excel file in onedrive. The details screen shows the info of the parent. I have the data set to UTC in the app and the date and time displaying correctly in Excel. I believe it is converting the UTC time to my timezone Eastern in Excel. In the app on the details page, the time is 5 hours off, the UTC time. How can i get the app to show Eastern time. For example, if i create a new record and set the time to 7 am, in excel, it lists it correctly. In the details screen of that record in the app it shows 2AM.
Nevermind, I figured it out. In the details data text i replaced Parent.Default with: DateAdd(ThisItem.StartDate, TimeZoneOffset(ThisItem.StartDate),Minutes)
I just tried this by applying it directly to the card default and saw that I needed to change "Parent.Default" to "ThisItem.[cardname]" and it worked. I believe if you're using this as a default for the card than it needs to ThisItem, if you're using it for a text field inside the card, then it would be Parent.Default...I believe.
I am trying to take the date from date picker and my data source is excel file in one drive .But,the problem is , when i edit the record or create a record the date value is stored one day less than the actual give date which is selected from date picker.
Hi @venkat4331 ,
Although I haven't tried with excel, I had a similar issue when saving to a SQL database and found that if I created a date only type instead of datetime, and saved a Today() instead of a Now(), as far as I can tell it worked as intended.
Maybe a similar type needs to be formatted for that column in Excel?
Maybe try testing it with a date column in the CDS?
I have an sharepoint list with a calculate field that calculates a date. In the display the date in a text control I have: DateValue(ThisItem.'sharepoint field'), it is ok because it shows me "Month 12 2016", but I have the same problem T when I view the records in a Gallery or otherwise pull the record up later, the date is early by one day.
Has anyone encountered any kind of "solution"?
Check out new user group experience and if you are a leader please create your group
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Participate in the Power Virtual Agents Community Challenge
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.