cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TheRydad
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
CarlosFigueira
Power Apps
Power Apps

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

20 REPLIES 20
MRCL
Advocate V
Advocate V

Same problem

mr-dang
Community Champion
Community Champion

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
CarlosFigueira
Power Apps
Power Apps

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
Frequent Visitor

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

 

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

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

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

 

I will try this solution later.

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

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
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,308)