Date Range Filtering

[Andrew_Enright]

Please add options within the filter UI that allow us to narrow a dataset to just records that meet X requirement within a period of time. In our case, we need to know when one field was changed to X today, yesterday, this week, last week and in the last two weeks.

Similarly, we need to be able to create views for today, tomorrow, next week, next two weeks, next month, next quarter, etc.

I’m sure this can be done via formulas/fields, but a UI is preferable for view-management.

Thank you.

[GSSJC_Backpacki]

I would also like to see date range filtering. The current options are very limited. I need to be able to set to filter results from Jan 1 through June 1, for example.

[Matt_Bush]

You can filter a date field to values within a certain range by combining 2 different filters on the date field:

Alternatively, you can write a formula that compares the date field to 2 values, and outputs 1 for values in the range, and 0 otherwise, and then filter for records for which this formula field equals 1. This is useful to express the date range comparison using a single field, so that it can be combined with other filters using “or”.

The equivalent formula for the above filters is:
AND(IS_AFTER({Event Date}, DATETIME_PARSE('2017-05-01')), IS_BEFORE({Event Date}, DATETIME_PARSE('2017-05-29')))

[Andrew_Enright]

Our work-around leverages two fields; Days From Release Date and This/Next Week. Here are our formulas:

Days From Release Date:

IF({Auto-Status} = “Complete”, “ :white_check_mark: ”,
IF({Auto-Status} = “Ghost!”, “ :ghost: ”,
IF({Auto-Status} = “Print Job Ticket”, “ :fax: ”,
IF({Auto-Status} = “Cancelled”, “ :skull: ”,
IF({Auto-Status} = “On Hold”, “ :zzz: ”,
IF({Release Date} = 0, “Release Date Needed”,
DATETIME_DIFF({Release Date}, TODAY(), ‘days’)
))))))

This/Next Week:

IF({Days From Release Date} > 12, “Soon”,
IF({Days From Release Date} = 12, “Next Week”,
IF({Days From Release Date} = 11, “Next Week”,
IF({Days From Release Date} = 10, “Next Week”,
IF({Days From Release Date} = 9, “Next Week”,
IF({Days From Release Date} = 8, “Next Week”,
IF({Days From Release Date} = 7, “Next Week”,
IF({Days From Release Date} = 6, “This Week”,
IF({Days From Release Date} = 5, “This Week”,
IF({Days From Release Date} = 4, “This Week”,
IF({Days From Release Date} = 3, “In Three Days”,
IF({Days From Release Date} = 2, “In Two Days”,
IF({Days From Release Date} = 1, “Tomorrow”,
IF({Days From Release Date} = 0, “Today :fire: ”,
IF({Days From Release Date} < 0, “Past Due :warning: ️”,
IF({Days From Release Date} = “Due Date Needed”, “Due Date Needed”,
""))))))))))))))))

It’s not smart, but covers our basic requirements. We’d of course welcome both more considered formulas and native UI for date filtering.

A.

[Nico]

Related with original request, we’d need predefined time ranges like “last week” (monday to monday, not the 7 past days), or last month, and so on. I tried to play with the available filters but I couldn’t get to this kind of behavior.

[Christoff]

You can create a Formula field and then filter by that field.

Let’s say you want to filter all records where the field “Due Date” has a date that occurs this week, meaning from Monday to Sunday.
Create a new Formula field and name it something like “This Week”.
The formula is:
IF(AND({Due Date}>=DATEADD(TODAY(),-WEEKDAY(TODAY())+1,‘day’),{Due Date}<=DATEADD(TODAY(),8-WEEKDAY(TODAY()),‘day’)),“Yes”,“No”)

This assigns the value “Yes” to the “This Week” field for any record where the Due Date is on or after Monday of this week and is on or before Sunday of this week. All other records will have the value “No”.

You can then create a View that filters on This Week = Yes. (You can use something else instead of “Yes” and “No”, e.g. 1 and 0. Just modify the formula accordingly and remember that text values in formulas require quotation marks, but numerical values do not.)

Likewise, you can use a formula for the previous week:
IF(AND({Due Date}>=DATEADD(TODAY(),-WEEKDAY(TODAY())-6,‘day’),{Due Date}<=DATEADD(TODAY(),-WEEKDAY(TODAY())+1,‘day’)),‘Yes’,‘No’)

And, next week (using 1 and 0 instead of Yes and No, just to show another way of doing it.)
IF(AND({Due Date}>=DATEADD(TODAY(),8-WEEKDAY(TODAY()),‘day’),{Due Date}<=DATEADD(TODAY(),15-WEEKDAY(TODAY()),‘day’)),1,0)

You could then have three filtered views: This Week, Last Week, Next Week.

Notice that the end of last week is the same as the beginning of this week, and the end of this week is the same as the beginning of next week. This means that anything scheduled at midnight on Monday morning (Sunday night) will appear on two filtered Views. You can change this by specifying “>” and “<” instead of “>=” and “<=” depending on how you want to treat events that are deemed to start at midnight, e.g. birthdays, anniversaries, holidays.

Also, the formulas assume that Saturday and Sunday are part of the week that started on Monday. I’ll leave it to someone else to come up with a way of dealing with work weeks (Monday to Friday) and having Sunday as the start of the week.

[Nico]

That’s pretty clever, many thanks!

I didn’t thought of getting it working via another field. It makes me wondering why we can’t just have some formulas in filters? Is it something planned?

[Jacob_Turley]

Or at the very least allow us “global fields” that we can filter off of. In cases like this I don’t want to have to go add a field to every table :slightly_smiling_face:

[Jacob_Turley]

JK It looks like this is there now for the most part!

[Alex_Githatu]

Same thing here, but with much less typing should your ranges change in the future:

IF({Days From Release Date} > 12, “Soon”,
IF(AND({Days From Release Date} <= 12, {Days From Release Date} >= 7), “Next Week”,
IF(AND({Days From Release Date} <= 6, {Days From Release Date} >= 4), “This Week”,
IF({Days From Release Date} = 3, “In Three Days”,
IF({Days From Release Date} = 2, “In Two Days”,
IF({Days From Release Date} = 1, “Tomorrow”,
IF({Days From Release Date} = 0, “Today :fire:”,
IF({Days From Release Date} < 0, “Past Due :warning:️”,
IF({Days From Release Date} = “Due Date Needed”, “Due Date Needed”,
"")))))))))

[Connor_Haslam]

Hey! Figured out a pretty easy way to do this to compare this week vs last week.

I made a formula:
WEEKNUM(TODAY())-WEEKNUM(Start)

If the result = 1, it was last week. If the result=0, it was this week. Then just make filter viewed for when result = 1 or 0.

Hope that helps!

Hope that helps!