Help

Re: Filtering based on time

898 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Boris_Demaria
4 - Data Explorer
4 - Data Explorer

Hi all,

One of the fields in my base is date/time, and I want a particular view to filter results to exclude anything created in the last hour (as an example) - what’s the best way to achieve this?

For context, all records in my base start out as a particular lifecycle status, I’m trying to capture the ones that get stuck in the first status for more than an hour. This would give enough time for the records that move more slowly through the statuses to be moved on and not appear in my filtered view.

1 Reply 1

Welcome to the community, @Boris_Demaria! :grinning_face_with_big_eyes: The default filter options don’t let you compare dates down to the hour, but a formula would. Try this:

DATETIME_DIFF(NOW(), CREATED_TIME(), "hours") <= 1

That will output a 1 when the created time of the record is within the last hour, and a 0 for all other records. You could then filter based the output of that formula. (If you want to switch to a manual date/time field, replace CREATED_TIME() with that field reference.)

While I’m here, here’s an important note from the formula field reference page about the behavior of the NOW() function that this formula uses:

This function updates when the formula is recalculated, when a base is loaded, or otherwise roughly every 15 minutes when a base is open. If the base is closed, it will update approximately every hour only when the base has time-dependent automation triggers or actions, or sync dependencies.