Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Need help correcting the NaN from my formula

Topic Labels: Formulas
Solved
Jump to Solution
830 7
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi there! I am looking to add to my formula (below and attached) that when the date is blank or 0 for the field to say “due today”

CURRENT FORMULA
DATETIME_DIFF({SHIP}, NOW(), ‘days’) & " " & IF(DATETIME_DIFF({SHIP}, NOW(), ‘days’) > 0, “days remaining :hourglass_flowing_sand: ”, “days overdue :boom: ”)

Screen Shot 2022-11-09 at 3.09.00 PM

1 Solution

Accepted Solutions

@Nicole_Abascal, try this:

IF(
  FIND("Shipped", {OVERALL STATUS}),
  "shipped",
  IF(
    {SHIP},
    IF(
      DATETIME_DIFF({SHIP}, NOW(), 'days') = 0,
      "due today",
      DATETIME_DIFF({SHIP}, NOW(), 'days') & " " &
      IF(
        DATETIME_DIFF({SHIP}, NOW(), 'days') > 0, 
        "days remaining ⏳", 
        "days overdue 💥"
      )
    )
  )
)

See Solution in Thread

7 Replies 7

Hi @Nicole_Abascal

You can wrap the whole thing in another conditional like this:

IF(
  OR(
    DATETIME_DIFF({SHIP}, NOW(), ‘days’) = BLANK(),
    DATETIME_DIFF({SHIP}, NOW(), ‘days’) = 0
  ),
  "due today",
  DATETIME_DIFF({SHIP}, NOW(), ‘days’) & " " &
  IF(
    DATETIME_DIFF({SHIP}, NOW(), ‘days’) > 0, 
    “days remaining :hourglass_flowing_sand:”, 
    “days overdue :boom:”
  )
)

Thank you so much for your help, Jeremy! I didn’t think anyone would respond.

Unfortunately formula did not work and I did not ask my question properly (apologies).

When the SHIP date is empty, I would like the SHIP COUNTDOWN to also be empty. When the SHIP date is the actual day (0 days), I would like the SHIP COUNTDOWN to read “due today.”

Aaahhh, gotcha, ok… let’s try this then:

IF(
  {SHIP},
  IF(
    DATETIME_DIFF({SHIP}, NOW(), 'days') = 0,
    "due today",
    DATETIME_DIFF({SHIP}, NOW(), 'days') & " " &
    IF(
      DATETIME_DIFF({SHIP}, NOW(), 'days') > 0, 
      "days remaining ⏳", 
      "days overdue 💥"
    )
  )
)

And I just realized that the reason the first formula didn’t work is because I forgot to fix the quotation marks in it to make them all ‘straight’ quotation marks instead of ‘curly’ ones… Airtable’s formula editor is really picky about that.

Thank you so much, Jeremy! It worked and appreciate your help/patience!!

Hey Jeremy, Could I have your wisdom on another component of the formula?

If the OVERALL STATUS has the status SHIPPED or PARTIALLY SHIPPED, I would like the formula to say “shipped”.

Screen Shot 2022-11-10 at 2.07.52 PM

@Nicole_Abascal, try this:

IF(
  FIND("Shipped", {OVERALL STATUS}),
  "shipped",
  IF(
    {SHIP},
    IF(
      DATETIME_DIFF({SHIP}, NOW(), 'days') = 0,
      "due today",
      DATETIME_DIFF({SHIP}, NOW(), 'days') & " " &
      IF(
        DATETIME_DIFF({SHIP}, NOW(), 'days') > 0, 
        "days remaining ⏳", 
        "days overdue 💥"
      )
    )
  )
)

Brilliant!! That did the trick and it looks incredible. Thank you so so much!