
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 26, 2020 07:12 AM
I’m getting stuck on this formula, please help! I have the following fields:
Status (single select)
Due Date (date)
I would like to have a new field “days past due” that shows the number of days past the due date IF the status is “past due”. Otherwise, leave the field blank.
I’m probably close (??) , but I tried the following two methods and am getting an error.
IF({{Due Date} >= TODAY()}, (DATETIME_DIFF()({Due Date},TODAY,‘day’)), " ")
IF({Status} = “Past Due”), ((DATETIME_DIFF()({Due Date},TODAY,‘day’)), " ")
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 26, 2020 08:56 AM
Hi @Ashley_Conway,
You can’t use math operators with Dates in Airtable, like you can with a lot of other systems. Instead, Airtable offers Date/Time specific formulas for these kinds of operations:
So I think you are looking for something like:
IF(
IS_AFTER(TODAY(), {Due Date}),
DATETIME_DIFF(TODAY(), {Due Date}, 'days')
)
or
IF(
{Status} = "Past Due",
DATETIME_DIFF(TODAY(), {Due Date}, 'days')
)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 26, 2020 08:56 AM
Hi @Ashley_Conway,
You can’t use math operators with Dates in Airtable, like you can with a lot of other systems. Instead, Airtable offers Date/Time specific formulas for these kinds of operations:
So I think you are looking for something like:
IF(
IS_AFTER(TODAY(), {Due Date}),
DATETIME_DIFF(TODAY(), {Due Date}, 'days')
)
or
IF(
{Status} = "Past Due",
DATETIME_DIFF(TODAY(), {Due Date}, 'days')
)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 26, 2020 09:05 AM
Your original formulas had issues with syntax:
- Field names go between curly braces:
{Field name}
- Parentheses surround a function’s parameters:
IF(condition, "value if true", "value if false")
. Some functions likeTODAY()
don’t take parameters, but they still need to be followed by a pair of parentheses.
So looking at your first formula:
-
{{Due Date} >= TODAY()}
is not a field name, it shouldn’t be within curly braces. -
(DATETIME_DIFF()({Due Date},TODAY,‘day’))
does not need the surrounding parentheses, the function properties forDATETIME_DIFF
are outside its parentheses, andTODAY
doesn’t have parenthesis after it at all.
You have similar issues with your second formula where the parentheses are in the wrong places.
Either of @Jeremy_Oglesby’s revised formulas should work.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 26, 2020 01:28 PM
Thank you! That solution worked. Wouldn’t it be great if we could use math operators with dates!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 26, 2020 01:41 PM
That’s not correct.
You can. There were two issues with your original formula, both of which were pointed out by @Kamille_Parks:
Neither of these has anything to do with the operators. After correcting those issues, this works fine:
IF({Due Date} >= TODAY(), DATETIME_DIFF(TODAY(), {Due Date}, 'days'))

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 26, 2020 01:45 PM
Good to know ⠀⠀⠀⠀⠀⠀⠀
