
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 19, 2019 02:53 AM
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?
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 19, 2019 04:48 AM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 19, 2019 04:48 AM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 16, 2019 10:30 AM
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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 16, 2019 11:16 AM
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”)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 02, 2020 06:46 AM
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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 05, 2020 06:54 PM
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}
)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May 02, 2022 10:38 AM
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”)
)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May 02, 2022 08:22 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May 03, 2022 07:41 AM
@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 …

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May 04, 2022 06:13 AM
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 toTrue
andFalse
respectively) as the output fromIF()
if theOR()
function returnsTrue
, which it always will because non-empty strings—“yes” and “no”—are equivalent toTrue
. - The second “yes” would be the output if the
OR()
were to ever returnFalse
(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 inDATETIME_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 forDATETIME_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"
)
