Unanswered

Date displayed is one day off

(3) Share

Report

Posted on by 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

Categories:

Building Power Apps

I have the same question (0)

All responses (22)

Answers (1)

MRCL 81 on at

Like (0)

Report

Same problem

Was this reply helpful? Yes No
Brian Dang 3,976 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

CarlosFigueira on at

Like (3)

Report
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

Was this reply helpful? Yes No
AJ-LL 4 on at

Like (0)

Report

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

Was this reply helpful? Yes No
ifti 86 on at

Like (0)

Report

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

Was this reply helpful? Yes No
ifti 86 on at

Like (0)

Report

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

Was this reply helpful? Yes No
TheRydad 17 on at

Like (0)

Report

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

I will try this solution later.

Was this reply helpful? Yes No
hhlaine 4 on at

Like (1)

Report

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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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

Was this reply helpful? Yes No
ToddBaginski 14 on at

Like (2)

Report

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

Was this reply helpful? Yes No