Date Specific Field Filters

[Aaron_Owen]

I’d like to have the ability to create a view based on records created or modified on a certain date. Specifically I’d like to create views for records created today and yesterday, as well as the ability to view records created on a specific date. Right now I can’t figure out how to create a view based on a relative date.

[Andrew]

Hey Aaron,

Unfortunately we don’t have modified and created fields yet, but we’re working on them:
https://community.airtable.com/t/date-field-autoupdate-on-last-change-addition/237

Note that if you do have a regular date field, you can filter down to relative dates like “today” and “yesterday”:

But for now you’ll manually have to add today’s date when you create a record in order for this to work.

[Aaron_Owen]

I was able to get a date created field by having a formula field that uses the CREATED_TIME() function, but since the field isn’t of type “Date” the specific Date filters don’t appear. Thus I cannot use the date specific filtering which is sort of a bummer.

[Fernando]

@Andrew I have same issue. Having a CREATED_TIME() column is not that useful if you cannot filter it as a date.

[Matt_Bush]

@Fernando If a formula date appears as a string, use the formula function DATETIME_PARSE() to turn it back into a date. That will allow you to use date filters.

CREATED_TIME() used in a formula by itself should be treated like a date; if you’re not getting date filters that may be a bug. You may get text-based output when using CREATED_TIME() in conjunction with other formula functions, though. Please email support@airtable.com if you’re still experiencing issues.

[Fernando]

Here is the function I am using: DATETIME_FORMAT(CREATED_TIME(),
‘MM/DD/YYYY’)

If i wrap the above in CREATED_TIME()then I get a date I can filter on
BUT in format MM/DD/YYYY/HH/MM

[Matt_Bush]

I see. So the behavior you’re describing is expected. DATETIME_FORMAT() outputs a text value rather than a date value, so it’s not possible to use date filters on a formula that uses DATETIME_FORMAT() at this time.

We’re currently exploring ways to support custom date formatting in formula-based dates while maintaining the ability to use date filters, so we should have a solution to this available in a few weeks.

Thanks for following up!

[Matt_Bush]

Looping back on this thread:

It’s now possible to configure date formats on formula, rollup, and lookup fields through a formatting tab. Regardless of the format you choose, the field will be treated as a date for filtering and sorting purposes. Enjoy!

[Aaron_Owen]

Woo hoo! This is great news.