Getting the current date/time takes a bit of work
Airtable NOW() and TODAY() will return values in Greenwich Mean Time (GMT). You will want, instead, to get your local time zone values, or your times will be off and for part of the day your dates will be off as well. One way to do this is create a Formula field that applies your local time zone to the current time along with the format you prefer. You can then reference it from any other formulas.
{Right Now}=
DATETIME_FORMAT(SET_TIMEZONE(NOW(), ‘America/Indiana/Indianapolis’), ‘M/D/YYYY hh:mm a’)
The Airtable field reference page refers to additional articles on the details of this, here’s a helpful one: (https://Formulas and date fields)
Just be sure, no matter how you use them, to wrap NOW() and TODAY() with code.
Testing Time Formulas
How do you create test cases for a base where the right answers will be different in 5 minutes? As time passes, any date formulas that depend in some part on the NOW() and TODAY() will change as time passes.
To enable test cases that I can repeat reliably, I’ve augmented my {Right Now] field formula above, to ‘freeze time’.
{Right Now} =
IF({Testing BE SURE THIS IS NOT SET TO ‘Testing!’ Otherwise your dates will be calculated incorrectly.}=“Testing”,
DATETIME_FORMAT({Testing Force Date},'M/D/YYYY hh:mm a'),
DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'America/Indiana/Indianapolis'), 'M/D/YYYY hh:mm a')
)
I’ve added a ‘testing’ field, and when I set it’s value to ‘Testing’, the {Right Now] function returns a fixed date and time. The fixed time is the contents of the field {Testing Force Date}. This has the effect of ‘freezing time’ so that the passing of time doesn’t change the expected result.
Adding Assertions into your Table
When I populate my base with test cases, I would like to be able to quickly know which rows are showing errors and which are OK. To do this, for my most complicated formulas, I add an ‘expected value field’, and another field that compares my expected value to the actual value. It displays ‘Error!’ if the two values don’t match. I then add a view that filters for ‘Error!’ on that field.
Those fields are hidden in almost all my views. When it comes time to use/release the base, I duplicate the base into a ‘release’ version, delete the test case rows in the ‘release’ version, and turn off testing.
