# Need help correcting the NaN from my formula

Topic Labels: Formulas
Solved
2767 7
cancel
Showing results for
Did you mean:
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: ”)

1 Solution

Accepted Solutions
14 - Jupiter

@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 💥"
)
)
)
)
``````
7 Replies 7
14 - Jupiter

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:”
)
)
``````
5 - Automation Enthusiast

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

14 - Jupiter

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.

5 - Automation Enthusiast

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

5 - Automation Enthusiast

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

14 - Jupiter

@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 💥"
)
)
)
)
``````
5 - Automation Enthusiast

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