cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mitchgramazio
Level: Powered On

Excel Data Filtering based on a Text "Date" Column

Hello all,

 

I've done a bit of searching on the site to make sure I'm not repeating a problem that's already been solved but I couldn't find a solution that worked for me.

 

I've got a column, in Text Format, titled "Date_Logged". I also have two DatePicker controls "contStartDate" and "contEndDate". I am trying to Filter() a table based on something being between the two dates picked in the controls. Currently I have:

Filter(
    Table1, contStartDate.SelectedDate <= DateValue("Date_Logged") And contEndDate.SelectedDate >= DateValue("Date_Logged")
)

I am using the DateValue function for the first time in my experience here, so it's possible that the error lies there. I am getting a Boolean value error for the Filter() function.

19 REPLIES 19
Super User
Super User

Re: Excel Data Filtering based on a Text "Date" Column

Hi @Mitchgramazio 

You have a bit of a syntax error in your filter.  The And is in all caps.  The actual And function is "And".

You can replace the AND with And and try that.  Also, you can use the && notation to provide the same And capablity...such as:

Filter(
    Table1, contStartDate.SelectedDate <= DateValue("Date_Logged") && contEndDate.SelectedDate >= DateValue("Date_Logged")
)


I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Mitchgramazio
Level: Powered On

Re: Excel Data Filtering based on a Text "Date" Column

Thank you,

 

I actually made that change already once realizing it. I'm still getting the error described earlier though.

Super User
Super User

Re: Excel Data Filtering based on a Text "Date" Column

@Mitchgramazio 

Can you provide a screenshot of the error and the formula showing the red underlines and any relevant error message?

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Super User
Super User

Re: Excel Data Filtering based on a Text "Date" Column

@Mitchgramazio 

Oh, and I just noticed you have quotes around your "Date_Logged".  Have you tried removing them?  On second glance, I believe that is possibly your issue.  You are trying to get the date value of "Date_Logged" (which would be nothing relevant).  But DateValue(Date_Logged) would be relevant.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Mitchgramazio
Level: Powered On

Re: Excel Data Filtering based on a Text "Date" Column

Attached is a screenshot. I've tried removing the quotations but nothing changed. I'm referencing a column in the Excel sheet which I believe requires the quotations for.

 

As an additional piece of information, this is in the Items property of the table.Screen Shot 2019-05-16 at 18.22.40.png

Super User
Super User

Re: Excel Data Filtering based on a Text "Date" Column

@Mitchgramazio 

Ah, now we're getting somewhere...that formula will not work that way.

 

You have a few conditions there based on the radio buttons and there are a few things you need to take into account.  First, your date range filter should go against the inner filter of the table you are searching.  Since that is a pain to put in over and over again, you can construct your formula like this:

Search(
   Filter(DiveLogs, 
          contStartDate.SelectedDate <= DateValue(Date_Logged) && contStartDate.SelectedDate >= DateValue(Date_Logged)
          ),
   Switch(Radio1.Selected.Value,
       "PERNER", TextInput3.Text,
       "Last Name", TextInput3.Text,
       "First Name", TextInput3.Text,
       "Hub ID", TextInput3.Text,       
       "Team", contTeam.SelectedText.Value,      
       "Location", contLocation.SelectedText.Value),
   Switch(Radio1.Selected.Value,
       "PERNER", "PERNER",
       "Last Name", "Name",
       "First Name", "Name",
       "Hub ID", "Hub_x0020_ID,       
       "Team", "Team",      
       "Location", "Location")
   )
      

In this case, we're filtering the DiveLogs table to start with and then placing that as the data source for the Search.  The second parameter is the text we want to search.  Since that varies based on your radio, the switch function is there to choose the right one.

The third parameter is the field we want to search.  Again, since that varies based on the radio, the switch function is there to choose the right one.

 

This could be crafted several different ways, but this should be pretty much what you're looking for.

Oh, and no...the fields should not have quotes around them when you are referencing them in this way.  Quotes around fields is only for certain parameters of some functions (like search).

 

Give the above a try and see if that gets you where you need to be...or at least closer.

 

I hope this is clear and helpful. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Mitchgramazio
Level: Powered On

Re: Excel Data Filtering based on a Text "Date" Column

Alright, I've replaced the code with the one you've provided. It returned an error saying that the Search function is expecting Text in the second paramter. I pruned through your code to make sure the assets matched what I am using in the application and it all checks out. I read more on how to use a Switch function and I see how that replaces the If syntax I had earlier. I'm unsure of why the Switch function isn't returning text values considering if a match is made, we've got it programmed to return a Text value from whichever radio button is selected.

Super User
Super User

Re: Excel Data Filtering based on a Text "Date" Column

@Mitchgramazio 

At a glance, without knowing your data specifically, I am suspecting "Hub_x0020_ID".  What kind of column is that.

You can ONLY use search on text columns.  You cannot use search to search a numeric column.

 

See if that is what you're up against.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Mitchgramazio
Level: Powered On

Re: Excel Data Filtering based on a Text "Date" Column

There is one column whose format is a custom number format and this preferrably stays this way. Am I able to use a Text() function or something similar?


Edit: I've removed both items that are not a generic Text format in Excel and the error remains.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 32 members 4,260 guests
Recent signins:
Please welcome our newest community members: