Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 12, 2021 05:14 AM
Hello, I have a table where every record has a time field. I would like to check a checkbox when the time is today at the specified time.
Using automations I managed to check the box when the date is today but the time is not considered. Is it possible to check the box at the exact time?
For example, I have a record that says 12/11/2021 1:00pm. The checkbox should be checked only after 1pm and not at any time of the day.
Thanks
Solved! Go to Solution.
Nov 12, 2021 08:24 AM
If you’re using the first formula, here’s a variation that will only output a 1 when the date exists and the current time has matched or passed it:
AND(Date, NOW() >= Date)
If you use the latter formula that outputs an emoji, you can use the same AND()
function inside that.
Nov 12, 2021 06:38 AM
Not necessarily at the exact time, but something could be created to check the box on or after that time. The problem is that Airtable’s NOW()
function that looks at the “current” time isn’t refreshed constantly. Per the documentation, it only updates roughly every 15 minutes when a base is open, and roughly every hour when it’s closed. If this inaccuracy is tolerable, here’s how to pull it off.
Create a formula field with this formula (assuming the name of your date field is {Date}
😞
NOW() >= Date
This will output a 1 when the current time is on or after your specified date. Now you can create an automation that triggers when that formula field outputs a 1, and updates the checkbox field for the triggering record.
However, if you just need some indicator of when the date and time has passed, you can do this without using an automation or a checkbox field and just alter that formula above to output a specific string when that time has passed:
IF(NOW() >= Date, "✅")
Nov 12, 2021 08:11 AM
Thanks for your answer. I have tried your solution but it seems to update all records with a “1” even where the date has not been added yet.
How can I make sure that the formula field is always 0 until it reaches the time?
Nov 12, 2021 08:24 AM
If you’re using the first formula, here’s a variation that will only output a 1 when the date exists and the current time has matched or passed it:
AND(Date, NOW() >= Date)
If you use the latter formula that outputs an emoji, you can use the same AND()
function inside that.
Nov 12, 2021 08:34 AM
That worked! I would have preferred an option where the NOW formula is refreshed constantly, but this will be ok until I find a better solution. Thanks