Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 11, 2016 07:00 AM
I cannot figure out how to filter all entries where “date” is the current month.
Solved! Go to Solution.
Oct 06, 2021 01:29 PM
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)
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.
Jul 11, 2016 12:08 PM
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
I suggest that you add the year value to the month fields ( i.e. 201610 for october 2016)
Jul 16, 2016 08:48 AM
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.
Jul 16, 2016 12:10 PM
Here’s another way to do it so it automatically switches each month:
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')
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
Mar 02, 2017 06:28 AM
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.
Jul 23, 2019 05:21 PM
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().
Sep 25, 2019 11:42 AM
Hey, thanks for sharing!
It looks like the link has either been unshared or deleted, any way you can reshare an updated link?
Nov 17, 2019 07:23 PM
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.
Nov 18, 2019 02:00 AM
Hi @Kasra_Kyanzadeh, is the value of the formula computed once when the formula is defined or is it dynamically computed when looked at ?
Jun 04, 2020 03:55 PM
Is it possible to use this formula for current week as well?