Help

Re: Need help correcting the NaN from my formula

Solved
Jump to Solution
2097 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Nicole_Abascal
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

Nicole_Abascal
5 - Automation Enthusiast
5 - Automation Enthusiast

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!