Date displayed is one day off

[TheRydad]

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

[MRCL]

Same problem

[mr-dang]

I also notice the same with DatePicker.

If I change the date forward, the actual date picked is one ahead. If I change the date backward, the actual date is picked one before.

[CarlosFigueira · Accepted solution]

You're hitting a current limitation in the DatePicker control (or the handling of dates in general). The issue is that if you set the default date as a text value with no time component and time zone set to UTC (the 'Z' at the end), it will convert to a Date value, and the date will be converted to a date in the local time zone. If you are anywhere west of UTC (i.e., almost all of the Western hemisphere, incluinding the Americas), then it will end up in the day before. You can see exactly which date the control is being given by displaying the following formula in a text box:

Text: Text(DateTimeValue("2016-05-12T00:00:00Z", DateTimeFormat.LongDateTime24))

We're working on a solution for this scenario (dates stored as UTC strings). There's a workaround that you can use in the interim, where you can calculate the exact delta that you would need to add to your date. In the example below, the date string is stored in a text input, and the logic is executed based on a button press, but it can be adapted for other controls (please let us know more details about your scenario if you need help).

TextInput1.Default: "2016-05-12T00:00:00Z"
Button1.OnSelect:
    UpdateContext({ d: DateTimeValue(TextInput1.Text) });
    UpdateContext({ dUtc: Text(Year(d), "0000") & "-" Text(Month(d), "00") & "-" & Text(Day(d), "00") & "T00:00:00Z" });
    UpdateContext({ dAdj: DateDiff(DateTimeValue(dUtc), d, Days) });
    UpdateContext({ dForPicker: DateAdd(d, dAdj, Days) })
DatePicker1.DefaultDate: dForPicker

[AJ-LL]

Has anyone found a solution for this ? Displaying date from sharepoint and editing them with a date picker always puts the date one day + or -.

[ifti]

Hi Carlos,

Thanks for your post.

I live in Australia and I'm having the same issue where the date chosen is going into a day before after the form is submitted via PowerApps.

I've tried to follow what one of your reps said but that's not working as it's expecting a "text"

DateValue(Left(Text(DateTimeValue(Parent.Default), DateTimeFormat.UTC), 10))

 Error Screenshot

What do you suggest?

Thanks

Ifti

[ifti]

I've now tried with the below code and that accepts it but still the same issue. Date is one day behind on PowerApps but is correct on SharePoint list.

DateValue(Left(Text(DateTimeValue(Text(Parent.Default)),DateTimeZone.UTC),10))

[TheRydad]

Thanks for the workI had forgotten to subbscribe to this thread and didn't realize anyone was repsonding.

I will try this solution later.

[hhlaine]

Any working solution in sight? Experiencing (date -1) issues with datepicker when storing dates into excel on onedrive

[dtowsey]

I have a simple text box that gets data from a SQL database and is based on a selection in a gallery. The date is off by one day. How would I use the workaround within this code?

Gallery1.Selected.Case_Initiation_Date