DATETIME_DIFF within an IF function

[Mathieu_Preau]

Hi there,

I need to calculate the difference between the {Date} and today in days, and display “todo” or nothing if the number of days is >1.

I tried this but I keep having errors :

IF(DATETIME_DIFF({Date}, TODAY(), ‘days’)>1),“To do”, " ")

Any ideas?

[Justin_Barrett · Accepted solution]

You’ve got an extra parenthesis in your formula after “>1”. Remove that and it works.

FWIW, you can also remove the " " at the end, as Airtable defaults to making the fallback value blank unless otherwise specified.

[Andrea_Sauceda]

Hello! I am trying to set up a field that will mark a project as “HOT” if there are less than 14 days between today and the Delivery Date.

I’m using the formula as corrected by @Justin_Barrett, but it’s not working… I’m not getting an error message, but it is also not marking records that are clearly less than 14 days from the delivery date. It’s not marking any records at all, in fact.

What have I done wrong?

IF(DATETIME_DIFF({Deliver By}, TODAY()< 14),“HOT”)

Thanks in advance!

Andrea

[Justin_Barrett]

Your closing parenthesis for DATETIME_DIFF is in the wrong place. You also haven’t specified the difference indicator (days, weeks, months, etc). Fixing both of those issues, it should look like this:

IF(DATETIME_DIFF({Deliver By}, TODAY(), "days")< 14,“HOT”)

[Kristina_Beever]

I’m having a very similar issue. I need to know if a date is within the next two weeks and if so, have it show up in the new field.

This is my formula:
IF(DATETIME_DIFF({Bids Due}, TODAY(), “weeks”)<2,{Bids Due})
I am getting the dates that are within two weeks, but it is also including dates that are past. What did I do wrong?

[Justin_Barrett]

Kristina_Beevers:

What did I do wrong?

The DATETIME_DIFF() function will return a negative value for dates in the past, and negative values are less than 2, which is why those entries match.

To make this work, you’ll need two comparisons: one to see if the difference is less than 2, and another to see if it’s greater than zero, and only show the results if both tests are true. Here’s how to do that:

IF(
    AND(
        DATETIME_DIFF({Bids Due}, TODAY(), "weeks") < 2,
        DATETIME_DIFF({Bids Due}, TODAY(), "weeks") > 0
    ), {Bids Due}
)

[Brittany_Claudi]

Hello! I’m trying to use this with an OR function. Essentially, if the time between two dates is more than 1 year OR the field is blank, I want to populate the field with “yes”. I’m definitely doing something wrong, though…advice?

EDIT TO ADD: I added the comma after 365, which removed the error, but now all fields are blank, so I don’t think it’s doing what I wanted it to…

IF(
OR(
DATETIME_DIFF({Today (date)}, {GD General Review Requested}, ‘days’) > 365
{GD General Review Requested} = BLANK(), “yes”)
)

[Justin_Barrett]

With that comma added, the structure of your formula looks good, so the issue must be with one of the pieces inside. What type of field is {Today (date)}? Is it an actual date field? Another formula field? If the latter, what is the formula? The same question goes for “GD General Review Requested”. The more details you can share, the easier it will be to help.

[Brittany_Claudi]

@Justin_Barrett Thanks for the reply! Today (date) is a formula field that is Today () so that it shows the current date. (I later realized that this is unnecessary as a field, as I can build this directly into the formula). GD General Review requested is a date field, some of which have a date, and some are blank. Essentially I want to know if it has been more than 1 year since the GD General Review date, and if so, write “yes” in a different column. If the GD General Review field is blank, I also want the new field to say “yes”.

My next iteration to try was this:
IF(
OR(DATETIME_DIFF({GD General Review Requested}, TODAY(), ‘days’) > 365, “yes”, “no”)
{GD General Review Requested} = BLANK(), “yes”, “no”)
)

Which is also not working …

[Justin_Barrett]

Thanks for the update. Upon closer inspection, the problem with your first formula was that a parenthesis was out of place. The “yes” string was being included as part of the OR() function, not as the result to return from the IF() function if the OR() function returned True. Moving that parenthesis like this would solve the problem:

IF(
  OR(
    DATETIME_DIFF(TODAY(), {GD General Review Requested}, "days") > 365,
    {GD General Review Requested} = BLANK()
  ), "yes"
)

The second formula has several issues.

• The first instances of “yes” and “no” are also inside the OR() function.
• The placement of {GD General Review Requested} = BLANK() right after that (after adding a comma, which was missing here as well) puts the results of that expression (a 1 or 0, equivalent to True and False respectively) as the output from IF() if the OR() function returns True, which it always will because non-empty strings—“yes” and “no”—are equivalent to True.
• The second “yes” would be the output if the OR() were to ever return False (which it never would because of those truthy strings)
• The final" no" would be ignored because the IF() function only accepts three arguments.
• Placing TODAY() as the second argument in DATETIME_DIFF() means that the output will be a negative number. Even if the other issues were fixed, the difference would never be greater than 365, so only a blank date field would trigger a “yes”. Your initial formula had those first two arguments for DATETIME_DIFF() in the proper order.

Here’s that second version reworked, including an optimization on how to test for an empty field without using the BLANK() function:

IF(
    OR(
        DATETIME_DIFF(
            TODAY(), {GD General Review Requested}, "days"
        ) > 365,
        NOT({GD General Review Requested})
    ),
    "yes",
    "no"
)