Help

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

Solved
Jump to Solution
961 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Ashley_Conway
4 - Data Explorer
4 - Data Explorer

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

1 Solution

Accepted Solutions
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

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

See Solution in Thread

5 Replies 5
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

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

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:

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

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

Screen Shot 2020-08-26 at 1.40.21 PM

Good to know ⠀⠀⠀⠀⠀⠀⠀