cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

Date displayed is one day off

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Power Apps
Power Apps

Re: Date displayed is one day off

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

View solution in original post

18 REPLIES 18
Highlighted
Advocate IV
Advocate IV

Re: Date displayed is one day off

Same problem

Highlighted
Community Champion
Community Champion

Re: Date displayed is one day off

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.

Microsoft Employee
@8bitclassroom
Highlighted
Power Apps
Power Apps

Re: Date displayed is one day off

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

View solution in original post

Highlighted
Frequent Visitor

Re: Date displayed is one day off

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

 

Highlighted
Advocate II
Advocate II

Re: Date displayed is one day off

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 ScreenshotError Screenshot

 

 

What do you suggest?

 

Thanks

 

Ifti

Highlighted
Advocate II
Advocate II

Re: Date displayed is one day off

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

Highlighted
Advocate I
Advocate I

Re: Date displayed is one day off

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

 

I will try this solution later.

Highlighted
New Member

Re: Date displayed is one day off

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

Highlighted
Helper I
Helper I

Re: Date displayed is one day off

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

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (6,709)