# Re: If Invoice is past due, display number of days past due

Solved
1069 0
cancel
Showing results for
Did you mean:
4 - Data Explorer

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’)), " ")

1 Solution

Accepted Solutions
14 - Jupiter

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')
)
``````
5 Replies 5
14 - Jupiter

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')
)
``````
16 - Uranus

• Field names go between curly braces: `{Field name}`
• Parentheses surround a function’s parameters: `IF(condition, "value if true", "value if false")`. Some functions like `TODAY()` 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 for `DATETIME_DIFF` are outside its parentheses, and `TODAY` 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.

4 - Data Explorer

Thank you! That solution worked. Wouldn’t it be great if we could use math operators with dates!

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'))
``````

14 - Jupiter

Good to know ⠀⠀⠀⠀⠀⠀⠀