Skip to main content
Solved

Correct Nesting Formula?

  • June 30, 2022
  • 4 replies
  • 34 views
R

Hi. I’m looking to track how much time (in days) a lead spends in each stage in a pipeline. I’m basing it on start and stop dates of time in stage. The formula I’m trying to accomplish is this (hope it makes sense):

Is the start date blank?

  1. YES: Do nothing, stay blank. (end)
  2. NO: (ask) Is the end date blank?
    —1. YES: Subtract today’s date from start date
    —2. NO: Subtract end date from start date

I came up with the following but I just get an error that the formula isn’t correct.

IF({Start Date} = BLANK(), “”, IF({End Date} = BLANK(), DATETIME_DIFF(TODAY(),{Start Date}, ‘days’), DATETIME_DIFF({End Date}, {Start Date}, ‘days’)

What’s not working?!

Definitely new to AirTable and formulas in general so if there is another way to approach this, I’m all ears! :). Thanks!

Best answer by Kamille_Parks11

The rest of your formula is a mathematic function that should result in a number, but your first condition results in an empty string.

In Airtable, you want to say “If field is blank do nothing”, you get the same result as “Do something if the field has a value” without risking putting strings where numbers/dates should be.

The other issue with your formula is its missing closing parentheses for the first IF.

IF(
  {Start Date},
  DATETIME_DIFF(
    IF({End Date}, {End Date}, TODAY()),
    {Start Date}, 
    'days'
  )
)

Since the DATETIME_DIFF function’s only variable was the first date, I just used the function once with an IF inside it.

4 replies

Kamille_Parks11
Forum|alt.badge.img+27

The rest of your formula is a mathematic function that should result in a number, but your first condition results in an empty string.

In Airtable, you want to say “If field is blank do nothing”, you get the same result as “Do something if the field has a value” without risking putting strings where numbers/dates should be.

The other issue with your formula is its missing closing parentheses for the first IF.

IF(
  {Start Date},
  DATETIME_DIFF(
    IF({End Date}, {End Date}, TODAY()),
    {Start Date}, 
    'days'
  )
)

Since the DATETIME_DIFF function’s only variable was the first date, I just used the function once with an IF inside it.

R
  • Ryan_Moore
  • Author
  • New Participant
  • June 30, 2022

The rest of your formula is a mathematic function that should result in a number, but your first condition results in an empty string.

In Airtable, you want to say “If field is blank do nothing”, you get the same result as “Do something if the field has a value” without risking putting strings where numbers/dates should be.

The other issue with your formula is its missing closing parentheses for the first IF.

IF(
  {Start Date},
  DATETIME_DIFF(
    IF({End Date}, {End Date}, TODAY()),
    {Start Date}, 
    'days'
  )
)

Since the DATETIME_DIFF function’s only variable was the first date, I just used the function once with an IF inside it.


This works perfect. I’ll study and keep in mind for the future. Thank you!

Kamille_Parks11
Forum|alt.badge.img+27

This works perfect. I’ll study and keep in mind for the future. Thank you!


I tend to simplify formulas down as much as possible. If you’re curious, the correct syntax for your original method would be:

IF(
  {Start Date} = BLANK(),
  BLANK(),
  IF(
    {End Date} = BLANK(), 
    DATETIME_DIFF(TODAY(),{Start Date}, 'days'), 
    DATETIME_DIFF({End Date}, {Start Date}, 'days')
  )
)
R
R
  • Ryan_Moore
  • Author
  • New Participant
  • July 1, 2022

I tend to simplify formulas down as much as possible. If you’re curious, the correct syntax for your original method would be:

IF(
  {Start Date} = BLANK(),
  BLANK(),
  IF(
    {End Date} = BLANK(), 
    DATETIME_DIFF(TODAY(),{Start Date}, 'days'), 
    DATETIME_DIFF({End Date}, {Start Date}, 'days')
  )
)

Yeah. This is a thing of beauty!

Terms & ConditionsAccessibility statement