Formula for recurring date or next date based on "...

[Joshi_Gottlieb]

Hi! I want to add a formula that does the following:

If “created on” is within the last week OR “revisit on” is today, then TRUE (i.e. check a box or write true in a text field).

“Created on” and “revisit on” are both date fields.

This way, I can better filter my view (my filter includes both “and” and “or” conditions, which is why I need to use a formula).

Thank you!!

[Joshi_Gottlieb]

Here’s what I tried, but the formula isn’t formatted correctly:

IF(DATETIME_DIFF({CREATED_TIME}, TODAY, ‘days’) < 7 OR({Revisit on}=TODAY), “True”, “False”)

[JonathanBowen]

Hi @Joshi_Gottlieb - when you do an “OR” the format isn’t:

Condition A OR Condition B

but:

OR(Condition A, Condition B)

So you’ll end up with something like:

IF(OR(Condition A, Condition B), 'True', 'False')

JB

(If this answers your question please mark it as “Solution” - if not, please post again. Thanks!)

[Joshi_Gottlieb]

Thank you for your reply!

Somehow I’m still getting the formatting wrong - Airtable won’t accept this formula:

IF(OR(DATETIME_DIFF({CREATED_TIME}, TODAY, ‘days’) < 7, ({Revisit on}=TODAY)), ‘True’, ‘False’)

[kuovonne · Accepted solution]

Welcome to the community!

You may need to edit the field names so that they exactly match your field names. Otherwise Airtable will not accept the formula.

You also need to check to make sure that the created time is greater than 0, or future dates will trigger the “true” value.

IF(
  OR(
    AND(
      DATETIME_DIFF(TODAY(), {created on}, 'days') <= 7,
      DATETIME_DIFF(TODAY(), {created on}, 'days') >= 0  
    ),
    {revisit on} = TODAY()
  ),
  "true"
)

---

Here are a couple of other cautions about formulas and date/time fields:

• The TODAY() formula is not updated continuously, you may need to reload a view in order to refresh the calculations.
• Values may end up off by a day, depending on what time zone you are in, how you have time zones configured, and when you view the data. Dealing with time zones can be a bit involved and usually requires a bit more information about the specific use case.

---

If this answers your problem, could you please mark this post as the solution?
If not, could you please give a bit more details on your use case and a screen capture?

[kuovonne]

Your formula was mostly correct, except for your use of the TODAY() function. You need to have the parentheses as part of the function call. Otherwise, Airtable thinks that you are looking for a field named TODAY, which you do not have.

---

If this answers your problem, please mark this post as the solution. Otherwise, could you please give a bit more details and a screen capture?

[Joshi_Gottlieb]

Thank you so much, this formula works – thanks also for telling me where I went wrong.

Do you know why the second record doesn’t return “true”? Since it should return “true” if it was created within the last week or if the “Revisit on” date is today:

Thanks again :slightly_smiling_face:

[kuovonne]

Good catch. You need to check if the created on date is greater than or equal to the TODAY, not just greater than. I edited my original formula with the fix.

[Joshi_Gottlieb]

This works, thank you sooo much!