Answered

DateDiff in Gallery Filter using Excel Column - date formatting issues

(0) Share

Report

Posted on by WrenAllElseFail

I'm effectively building an inventory management app, and I'm essentially trying to emulate prompted cycle counts. I have an excel spreadsheet stored on SharePoint. My issue is the Gallery items.

I am running a filter and (for example) I want the filter to return any items for which the time between since the last count (a column in the spreadsheet) and today is at least a certain number of days, months, etc. I keep getting an error on the time format. I've tried running it with the text function in a couple of spots, I've tried changing the format settings for the cells in that column in Excel, but I can't seem to get it to work right again. I've also tried running the date format function (dateformat.longdate) but that doesn't help and/or getting an error that says longdate is invalid.)

So effectively I want my Items formula to be Filter(DataTable,ItemColumn,DateDiff(Today(),LastCountedColumn)>=7). I'll be building other conditions into my filter after but this is the one I'm struggling with right now.

I don't have the exact formula in front of me to share at the moment, but can share later if needed. Can someone please help me with how to make the date functions talk with my excel sheet column, and how to apply the >= logic if that is incorrect? TIA!

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (2)

Sort by

Verified answer

WarrenBelz 156,409 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @WrenAllElseFail ,

Firstly Excel and Dates in Power Apps are not the best of friends - if you have the field stored as Text, it would be something list this (this may be reversed if the dates are before today (you would need <= -7)

Filter( DataTable, DateDiff( Today(), DateValue(LastCountedColumn) >= 7 ) )

If you have SharePoint lists available, this would be much easier (Excel is a terrible data source for many reasons)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
WrenAllElseFail 56 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you! I'll give this a shot tomorrow and report back. So you're saying it *should* be formatted as text? My column reads ex: 02/23/2024 and it is formatted is a date. Also, so there's no need to run a formatting function or tell PA what format I want it in? What if I want it to return months instead of days?

I'll look into converting from Excel to SharePoint. Can it generally accommodate the other functions of excel? Does it handle charts /graphs well? Etc. I've used excel because it's comfortable and familiar but I've heard that SharePoint is better from a data person. My spreadsheet is maybe a few hundred or so rows, if it matters.

Was this reply helpful? Yes No
Verified answer

WarrenBelz 156,409 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @WrenAllElseFail ,

Two other issues of using Excel for Power Apps is that you cannot have Excel open outside Power Apps while anyone is connected to it, nor can you generally make any changes to the table outside of Power Apps. If you want to use Excel charts and graphs, you can export to Excel directly from SharePoint and then paste that into your chart template. If you have Excel formatted as a date (rather than Text), to get this value back in Power Apps, you need

DateAdd( Date(1900,1,1), YourExcelDate -1 )

Excel also is quite restrictive and inconsistent (some things just do not work) and this grows more as the table gets bigger.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
WrenAllElseFail 56 on at

Like
a
(0)

Report
Copy link

Link copied!

Is this also true of Power Automate? I have a different project where my flow adds rows to excel which users then need to be able to edit directly. An action in the flow relies on a formula in one of those columns, and I also have tabs that keep running calculations from the first, as well as one that provides data for a drop down. I think the drop-down is easily surmountable.

Was this reply helpful? Yes No
WarrenBelz 156,409 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@WrenAllElseFail ,

Power Automate I believe interacts differently with Excel. I have tried to give you advice that is shared (please watch this video) broadly, however if you want to use Excel, just be prepared for some frustration.

Was this reply helpful? Yes No