If Invoice is past due, display number of days past due

[Ashley_Conway]

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

[Jeremy_Oglesby · Accepted solution]

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:

Airtable - Support

Formula field reference

  Join a live training! Unlock more power with Airtable   For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve fun...

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

[Kamille_Parks]

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 like TODAY() don’t take parameters, but they still need to be followed by a pair of parentheses.

So looking at your first formula:

Ashley_Conway:

IF({{Due Date} >= TODAY()}, (DATETIME_DIFF()({Due Date},TODAY,‘day’)), " ")

• {{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.

[Ashley_Conway]

Jeremy_Oglesby:

IF(
   {Status} = "Past Due",
   DATETIME_DIFF(TODAY(), {Due Date}, 'days')
)

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

[Justin_Barrett]

Jeremy_Oglesby:

You can’t use math operators with Dates in Airtable, like you can with a lot of other systems.

That’s not correct.

Ashley_Conway:

Wouldn’t it be great if we could use math operators with dates!

You can. There were two issues with your original formula, both of which were pointed out by @Kamille_Parks:

Kamille_Parks:

• {{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.

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

[Jeremy_Oglesby]

Good to know ⠀⠀⠀⠀⠀⠀⠀