Skip to main content

Hey All,

I have a database of research findings, and I’ve been asked to prepare a report that lists the most recent findings so I’ve been trying to construct a table view that only shows records that have been modified in the past week or so; however, I’m not sure what’s the best way to do this.

TONOW(LAST_MODIFIED_TIME()) gives me a column with the elapsed time since the last modification, but when I try to wrap it in a conditional which I can then filter by…

IF(TONOW(LAST_MODIFIED_TIME()) <= 14, “Recent”, “Previous”)

…I just get a column full of “Previous” even when it’s clearly wrong.

I imagine it’s because the two sides of the conditional are different data types (7 days vs 14) and so the boolean is failing to work as intended, but I can’t figure out how to cast the first side as an integer and changing “14” to “14 days” makes the formula invalid.

Would love some help or advice on a better direction as I feel like I’m missing something obvious! Thanks in advance! :slightly_smiling_face:

Adam

You could try DATETIME_DIFF() instead of TONOW():

IF(DATETIME_DIFF(NOW(),LAST_MODIFIED_TIME(),'days') <= 14, 'Recent', 'Previous')

You could try DATETIME_DIFF() instead of TONOW():

IF(DATETIME_DIFF(NOW(),LAST_MODIFIED_TIME(),'days') <= 14, 'Recent', 'Previous')


Thanks for the advice Kamille! I added your suggestions to the formula.

I actually ended up figuring out the reason it wasn’t working was because I was doing something stupid. I was not taking into account that editing the formula was constantly updating the return value from LAST_MODIFIED_TIME() so it would be constantly telling me the record was changed recently.

Once I used a column with a Last Modified Time type that was only tied to specific key fields in each record instead of the LAST_MODIFIED_TIME() formula, the modified time was less variable, and I was able to get the formula to only pull the records I had made major, significant changes to.

In the future, I might look into ways of passing in the desired key columns into LAST_MODIFIED_TIME() and thus removing the need for a seperate modified time type column, but for now it is working. :slightly_smiling_face:

Thanks for the help!

Adam

Terms & ConditionsAccessibility statement