Formula for filtering a date range

[johneodell]

I have a simple formula for filtering data to pick up only a specific date range. However when testing it I discovered I am only getting the day before the last date in the range. 

Here is the formula:

[endingDateTime] >= '2019-12-01'
AND
[endingDateTime] <= '2019-12-31'

I'm getting results from 12/1 to only 12/30 with the 12/31 results being left out (and yes, there is data for the 12/31 date)

I have a feeling that I am missing something very simple and obvious...

[NickSm]

Hey @johneodell 

This is in the "Custom Filter" section of a Filter tool? All looks correct, but can you share the workflow or a sample of the data with that date field?

Alternatively you could try just a < '2020-01-01' as your filter and see if that gets you the expected results.

[johneodell]

@NickSm 

Yes, it's in the "Custom Filters" section of the Filter tool. It's also one of the first tools after a SQL query to fetch the data from the db. There is no manipulation of the date fields prior to this filter.

I have included an example of the dates.

[DiganP]

@johneodell Seems to work for me. Attached is the workflow.

Make sure that the endingDateTime is a Date format:

[DiganP · Accepted solution]

@johneodell Just ran the workflow with your dataset, it seems to work fine? Can you attach a screenshot and your tool configuration. 

[johneodell]

@DiganP  Here you go

Nothing unusual. The Find and Replace tools are appending data that has nothing to do with dates. Furthermore, I can see ALL the dates in my "prefilter" output file. The "postfilter" output shows the the filter results with all the of the 12/31 dates missing. I know I'm doing something dumb that I need a second set of eyes to notice.

[johneodell]

@DiganP @NickSm 

I'm not sure what happened but I worked on other things, came back to my workflow, ran it again, and it seemed to work. I don't know why or how, but I'm just going to accept it. Thanks for the input!

[JTB76]

Hi - I am trying to get a filter done based on June-2020 format.  I need to be able to pull back everything that's between November 2020 and March 2021 inclusive.  I have tried the formula below with no luck.  Any ideas?

[JTB76]

Sorry, this formula:

[Plan Approval Month and Year] > 'November-2020'
AND
[Plan Approval Month and Year] < 'April-2021'

[Jean-Balteryx]

Hi @JTB76 ,

Here is a workflow that filters your data.

To do what you need you have to use date format values. To do so you can use the DateTimeParse function to transform your field as a date one then you can compare it to date values.