Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Date filter - this/current month

Solved
Jump to Solution
6567 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Maszer_Dess
5 - Automation Enthusiast
5 - Automation Enthusiast

I cannot figure out how to filter all entries where “date” is the current month.

1 Solution

Accepted Solutions

Yah, it’s frustrating, but this method I’ve captured below will at least work so that you can do what you need to do.

(DATETIME_FORMAT({Test_Date_Time}, 'MM') - DATETIME_FORMAT(TODAY(), 'MM')) +((DATETIME_FORMAT({Test_Date_Time}, 'YYYY') - DATETIME_FORMAT(TODAY(), 'YYYY')) * 12)

image

It’s worth reaching out to Airtable Support with this feature enhancement request, considering they’re actively working on the filters (which is epic!) and released new and/or group search features this morning.

See Solution in Thread

14 Replies 14
Mics_Sky
6 - Interface Innovator
6 - Interface Innovator

Hi !
The filter needs a field and a constant (a value)
or
A field and a second field to work.

I don’t think that you can use a formula (?!)

So you may need to first ADD those fields before using them in a filter.

i.e

  • Field “this_month” of type FORMULA
  • Field “Month_of_my_dateField” of type FORMULA

I suggest that you add the year value to the month fields ( i.e. 201610 for october 2016)

Peter_Todd1
5 - Automation Enthusiast
5 - Automation Enthusiast

You’ll need to do a compound filter.

Make the first line: Where | Date | is on or after | the first day of the month
Make the second line: Where | Date | is on or before | the last day of the month

This will return the results of days between the two dates.

Sadly you’ll have to re-select the dates each month, it’s not automated.

Kasra_Kyanzade1
6 - Interface Innovator
6 - Interface Innovator

Here’s another way to do it so it automatically switches each month:

  1. Create a formula field in the table called “Is current month”. We want this formula to be 1 when the date is in the current month, and 0 otherwise. So we can use this formula:

    DATETIME_FORMAT({Name of your date field}, 'YYYY-MM') = DATETIME_FORMAT(NOW(), 'YYYY-MM')

  2. Now you can create a filter where “Is current month” is equal to 1.

Here’s a base that shows this set up: https://airtable.com/shr1WzfJyhsdV5HlV/tblY9aNHNVFjtgP1v/viwau4UAl4y9H0DWl

This solution worked perfectly. Thank you!

Would you happen to have a similar solution for displaying all records from the previous month? For example…Today is March 2nd (or any date in March) and I want to see records only from last month (February). Do you have a similar automated solution that will adjust every month? In April, previous month filter will only show March records. May will show April records, etc.

I know this is old but for anyone else coming across this, here’s the answer:

DATETIME_FORMAT({Delivered Date}, "YYYY-MM") = DATETIME_FORMAT(DATEADD(NOW(), "month", -1), "YYYY-MM")

It takes the time NOW() and subtracts one month from it using DATEADD().

Hey, thanks for sharing!
It looks like the link has either been unshared or deleted, any way you can reshare an updated link?

nnnnneil
8 - Airtable Astronomer
8 - Airtable Astronomer

This is great. One final twist. How would you formulate previous month in previous year? E.g. It’s Nov 2019 now, so I want a formula to indicate Oct 2018 rows.

Hi @Kasra_Kyanzadeh, is the value of the formula computed once when the formula is defined or is it dynamically computed when looked at ?

Is it possible to use this formula for current week as well?