Formula resulting in NAN

[Nicole_Abascal]

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: ”)

[Jeremy_Oglesby]

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:”
  )
)

[Nicole_Abascal]

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.”

[Jeremy_Oglesby]

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.

[Nicole_Abascal]

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

[Nicole_Abascal]

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”.

[Jeremy_Oglesby · Accepted solution]

@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 💥"
      )
    )
  )
)

[Nicole_Abascal]

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