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).
Thanks!
-rd
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)
Is there a way to remove the 00:00 from this formula??
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 having the same issue. Can you please share screen shoot ? on which property did you add the formula ?
Hi,
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"?
Thanks,
I am facing this issue till today i.e. in Aug-2021. Is fix available for this?
User | Count |
---|---|
257 | |
108 | |
93 | |
57 | |
40 |