Skip to main content
Solved

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

  • August 26, 2020
  • 5 replies
  • 96 views
A

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

Best answer by Jeremy_Oglesby

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

5 replies

J
Forum|alt.badge.img+18

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')
)
Kamille_Parks11
Forum|alt.badge.img+27

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.

kuovonne
A
  • Ashley_Conway
  • Author
  • New Participant
  • August 26, 2020

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

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

Justin_Barrett
Forum|alt.badge.img+21

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

J
J
Forum|alt.badge.img+18

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


Good to know ⠀⠀⠀⠀⠀⠀⠀

Terms & ConditionsAccessibility statement