Filter Date is filtering Date +1

[Anonymous]

Hello guys, I have a Gallery with some dates and infos, and I need to use the DatePicker to filter.

The date format i needed to use was : Text ( DateAdd(ThisItem.Data; TimeZoneOffset(ThisItem.Data);Minutes) ; "[$-pt-BR]dd/mm/yyyy" ; "pt-BR" ). because it was showing Date + 1

But now my Issue is that the DatePicker, when filtered, the Gallery is filtered only the Date + 1 and others dates.

Here is the screenshot:

I wanted that the same date in the DatePicker appeared in the gallery, maybe is something about the TimeZone again...

But where?

I appreciate any helps!

[CarlosFigueira]

Can you provide more information, especially the following:

• Did you customize any properties on the date picker?
• What is the Items property of your gallery?
• Where is the expression you showed (Text(DateAdd(...))) used in your screen?
• Which data source are you using, and what is the type of the column that holds the date?

With this we should be able to better understand your problem.

[Anonymous]

• Did you customize any properties on the date picker?

No, the properties is all Default..

• What is the Items property of your gallery?

The items are those ones wich I get the data from a excel table:

The datas are Work Hours from My employees, ex: from 07 am to 15 pm, Jairo & Kleber will work.

"REPOUSO" or "FOLGA" are the same means that they are resting in this day.

"OUTRO" or "FÉRIAS" is vacation of one employee.

• Where is the expression you showed (Text(DateAdd(...))) used in your screen?

This expression is on the DATE on the gallery to bring the correct date according to the Excel table, otherwise it will bring wrong.

• Which data source are you using, and what is the type of the column that holds the date?

Its from Excel Table, the data column is is a Date Format Colum, screenshot is above.

[CarlosFigueira]

---

@Anonymous wrote:

• What is the Items property of your gallery?

The items are those ones wich I get the data from a excel table:

I'm guessing the Items property of the gallery is a Filter expression that references the date picker's SelectedDate value. Can you post it here as well?

[Anonymous]

Yep, I use this formula to filter :

Sort(Filter(TableEscala; Data >= DatePicker1) ; Data)

Note that, even the ">=" appears, the datepicker shows the date but the gallery shows Date + 1...

[CarlosFigueira · Accepted solution]

Got it. In this case, you'll need to account for the time zone offset when looking at the value from the date picker as well:

Sort(
    Filter(
        TableEscala;
        Data >= DateAdd(
                    DatePicker1.SelectedDate;
                    -TimeZoneOffset(DatePicker1.SelectedDate);
                    Minutes);
    Data)

The reason why you need to do this is that how date/time values in Excel and in PowerApps are represented are different. Excel represents date/time columns as absolute values, while PowerApps represents them as a point in time. The blogs post at https://powerapps.microsoft.com/en-us/blog/working-with-datetime-values-in-sql has more information about it (it talks about SQL, but it's the same issue with Excel).

[Anonymous]

Got it Carlos. but your formla encountered an error in Filter....=(

Edited: I fixed the formula and it worked well, thanks so much Carlos!

[Anonymous]

Carlos, do you know why everytime someone open the App, this error apperas:

Something like: It was expected a value from valid date as parameter for the function 'TimeZoneOffSet', value received: null.