Filter List by date range


I am trying to create a filter for my app that will show me all the rows of data that are between two dates & times.

The data from my table that it will filter for is formated as date & time (example: 8/24/2020 12:43PM).
In my app I set my app filters to input values under variables $[From Date] and another for $[To Date]. Then created a filter for the source of my Stacked List that you can see in the screenshot below

As you can see I filter for:
=Filter(Eat,"((Eat[Date & Time]>$[From Date] AND Eat[Date & Time]<$[To Date]) OR ($[From Date]=""""))")

However as a result when I input my From Date and To Date in my app filters it results in no results even thought there should be data to list within the date range.

Any idea what the issue is? Is my filter written out correctly?

Hi @Milly,

Thanks for posting and welcome to the community!

It's neat to see what you're building too, that's a great idea for an app. :slight_smile:

If you'd like your list to show data only between those two input values, this is how you would write your formula:

=Filter(Eat,"Eat[Date & Time]>=$[From Date] AND Eat[Date & Time]<=$[To Date]”)

Now when you select a date and time in both of those input fields, it should display the data from your "Eat" table within that date and time range.

Let me know if that works. :honeybee:

Thanks @Alyssa! I also think what was causing the issue is the manual time inputs I put in my table. Looks like the format didn't capture properly. Either way the new formula you provide works!

UPDATE: Just confirming it was an issue with the manual time inputs for some reason even though the format is applied on the manual inputs the way it's logged is different.
Even though format and display preference for all cells are 'Date & Time' and to be displayed as 1/2/17 3:04 PM, the 'times' that are getting picked up by formula are cells that show the date & time as something like 8/26/2020 9:43:20 AM when you click into the cell vs the ones that caused the issue where the formulas were not working are showing as 8/24/2020 10:58AM when you click into the cell. Seems like on the tables the data was being saved differently which could have caused the formulas not to work on all data it was looking at.

1 Like

Hi @Milly,

Awesome, I'm happy that worked for you! :slight_smile:

And thanks too for sharing what you were seeing with the manual inputs -

That's good feedback that I can share with the team, and we can take a deeper look into how we can improve that in Honeycode. (In helping validate that data is correctly formatted / properly read as the expected format).

Hi @Milly, sounds like @Alyssa was able to help you; which is great! I thought I'd share a similar filter between dates post in case you wanted to compare/contrast notes: How to present a screen with summary totals

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.