cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

PowerApps Excel Date Filtering Problem

Hello everyone.  I have an Excel table in OneDrive connected to a PowerApp gallery.  One column in the Excel table is 'week starting' and a date in format mm/dd/yyyy.  Other columns are people's names.  I want to filter to a specific date, example 06/11/2018, and bring back the results from the other columns.  

 

My problem is that even though the Excel column is formatted as date and only includes the date (not time), PowerApps brings back a time value as well (example: PowerApp returns 06/11/2018 7:00 PM instead of just the date).  I do not know where it's getting this time value.  So, when I do my filtering on the gallery, I can never seem to get a specific date.  I have to choose 'greater than or equal to' or it will return zero results:

 

Filter(Table1,Week_x0020_Starting = 06/11/2018)                   THIS RETURNS NO RESULTS

Filter(Table1,Week_x0020_Starting >= 06/11/2018)                 THIS RETURNS RESULTS, but the entire table from start to finish.

 

I don't believe this is a time zone issue as I've done a Now() and it appears correct.  I just can't seem to grasp how PowerApps is handling dates in Excel tables, and then how I need to work with them in functions/formulas.  Does anyone know how to do this?

 

TLDR;

Need to know how to filter a gallery connected to an Excel table that has a date column, then return 3-5 values from resulant columns.

1 ACCEPTED SOLUTION

Accepted Solutions
Shanescows
Super User
Super User

Long story short here I have found that Excel dates and PowerApps get grumpy with each other. What I have done in these situations is changed the column to just be text. Then when I get the date into PowerApps I use DateValue to convert it to a date object. Then before sending it back to Excel make it text again. 

 

Not ideal but that has worked for me.

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training

View solution in original post

5 REPLIES 5
Shanescows
Super User
Super User

Long story short here I have found that Excel dates and PowerApps get grumpy with each other. What I have done in these situations is changed the column to just be text. Then when I get the date into PowerApps I use DateValue to convert it to a date object. Then before sending it back to Excel make it text again. 

 

Not ideal but that has worked for me.

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training

View solution in original post

v-micsh-msft
Community Support
Community Support

If the column in your Excel table has been formatted into DateType, then please take a try to switch the formula under the items property to the following:

Filter(Table1,
         DateValue(
            Text(Week_x0020_Starting)) = Date(2018,6,11)
         )

Adding the function reference:

DateValue function in PowerApps

Text function

Date and Time function

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

I took your advice and made a new column with just text versions of the date column I previously used.  I have to then use a DateValue function to use these values as dates in PowerApps, but it saves me from having to deal with the weird date translation issues between PowerApps and Excel.  The solution referenced below would work, but it's inefficient as it converts a date field in Excel to text date in PowerApps, then back to a date again in PowerApps to be able to work around the date issues.  

 

This would be a great opportunity for Microsoft developers to improve this particular interaction between PowerApps and Excel.  Working with dates is already complicated, so having to deal with strange translation issues like this makes it even more difficult.

 

Thanks!

Could you please take me step by step on how to do that?

I have run into same problem today.

Excel date is in numeric format therefore I have converted PowerApp date to number and deducted static value: Value(PoweAppDate)/86400000+25569.04166666

 

86400000-represent 1 day in milliseconds

25569.04166666 difference between day 1 in excel (year 1900) and power app (year 1950)

 

now I can compare numeric date value of excel and powerApp : 

ExcelDateVal = Value(PoweAppDate)/86400000+25569.04166666

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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