Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

Date Range Filtering

cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Andrew_Enright
9 - Sun
9 - Sun

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.

12 Comments
GSSJC_Backpacki
5 - Automation Enthusiast
5 - Automation Enthusiast

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
Airtable Employee
Airtable Employee

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

29cb6bc0219ec6e2b667fb8f036d4b30d5ee432e.png

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
9 - Sun
9 - Sun

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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
7 - App Architect
7 - App Architect

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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
6 - Interface Innovator
6 - Interface Innovator

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
6 - Interface Innovator
6 - Interface Innovator

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

Alex_Githatu
4 - Data Explorer
4 - Data Explorer

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
4 - Data Explorer
4 - Data Explorer

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!