What’s an efficient way to see if a time of day is after another.
For example, let’s say I have a client who says, “please call me only after noon.”
How should I structure a column (text? datetime?) containing their stated time and a second column (for a logical test) to tell me if it is ok to call at the current time?
Thanks.
Still having this issue, seems like we should be able to do something like:
NOW() - {Some Time}
But that doesn’t work.
+20Still having this issue, seems like we should be able to do something like:
NOW() - {Some Time}
But that doesn’t work.
Time differences are calculated using the DATETIME_DIFF function. Details are on the formula field reference page:
In your case, you’d compare between NOW() and the time you specify in another field.
However, be aware that, according to the documentation for NOW() and TODAY(), “Note that the results of these functions change only when the formula is recalculated or a base is loaded. They are not updated continuously.”
The latter trigger (“a base is loaded”) was the easier one to test, but I couldn’t just go back to the workspace list, then back into the same base. I had to literally switch to a different base, then back to the one in which I was running the test.
The more challenging trigger is “when the formula is recalculated.” Unfortunately there’s no button to click to refresh all formulas, and figuring out what actually triggers a recalculation hasn’t been easy. Simply editing the field options, then clicking “Save” doesn’t do it. I tried changing my test formula from calculating the difference in seconds to minutes, but it didn’t have any affect. I added a space to the end of the formula, and that initially forced a recalculation, but then removing the space didn’t, nor did re-adding the space again. I then added “+1” to the end of the formula, thinking that surely that would force a recalculation of NOW(), but it didn’t. I then wrapped DATETIME_DIFF inside an IF that checked the state of a checkbox field, but that didn’t help.
I was about to type, “Looks like base-switching is your best bet,” but I just tried it again and nothing happened. :man_shrugging:
Does anyone else know a reliable trigger to force a formula to recalculate?
Time differences are calculated using the DATETIME_DIFF function. Details are on the formula field reference page:
In your case, you’d compare between NOW() and the time you specify in another field.
However, be aware that, according to the documentation for NOW() and TODAY(), “Note that the results of these functions change only when the formula is recalculated or a base is loaded. They are not updated continuously.”
The latter trigger (“a base is loaded”) was the easier one to test, but I couldn’t just go back to the workspace list, then back into the same base. I had to literally switch to a different base, then back to the one in which I was running the test.
The more challenging trigger is “when the formula is recalculated.” Unfortunately there’s no button to click to refresh all formulas, and figuring out what actually triggers a recalculation hasn’t been easy. Simply editing the field options, then clicking “Save” doesn’t do it. I tried changing my test formula from calculating the difference in seconds to minutes, but it didn’t have any affect. I added a space to the end of the formula, and that initially forced a recalculation, but then removing the space didn’t, nor did re-adding the space again. I then added “+1” to the end of the formula, thinking that surely that would force a recalculation of NOW(), but it didn’t. I then wrapped DATETIME_DIFF inside an IF that checked the state of a checkbox field, but that didn’t help.
I was about to type, “Looks like base-switching is your best bet,” but I just tried it again and nothing happened. :man_shrugging:
Does anyone else know a reliable trigger to force a formula to recalculate?
Hmmm. Thanks.
I thought I had tried this previously, but I guess I hadn’t. Assuming I enter my client’s desired call back time of day as a full date, i.e. a Date type field (where the day will be ignored in the calculation), then this works:
IF(TIMESTR(NOW())>TIMESTR(myDate),TRUE(),FALSE())
As far as recalculation goes, I’ve been contemplating that as well. I just refresh my browser, which I know is crude but has been reliable. It’s also on my list to try creating some mundane recurring interaction with Zapier and perhaps some other app as a trigger to see if that will force a recalc.
+20Hmmm. Thanks.
I thought I had tried this previously, but I guess I hadn’t. Assuming I enter my client’s desired call back time of day as a full date, i.e. a Date type field (where the day will be ignored in the calculation), then this works:
IF(TIMESTR(NOW())>TIMESTR(myDate),TRUE(),FALSE())
As far as recalculation goes, I’ve been contemplating that as well. I just refresh my browser, which I know is crude but has been reliable. It’s also on my list to try creating some mundane recurring interaction with Zapier and perhaps some other app as a trigger to see if that will force a recalc.
Glad that you got something working. I hadn’t thought about browser refreshing, mainly because I use the desktop app a lot more than the browser version. Closing and reopening the desktop app would also work, but there’s got to be an easier way. I just don’t use either NOW() or TODAY() enough to spend the time to figure it out.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
