Formula problem - needing to convert an NaN to "0"

[Scott_Ogier]

Hi there,

I am a relatively new Airtable user with a basic understanding of formulas which has held me in good stead until this week.

I have a table linked to a form. It’s a form designed to use for a customer to hire various types of product for us. The form populates all the required information into a table where it shows all the relative information to us about what product they are hiring. The products are theatrical props and theatrical costumes.

In the table, there are fields for “collection date” and “return date” for each above category and currently I have a field called “number of days hired” for each category that is reading those dates and working out the amount of days that product is hired by the current formula:

DATETIME_DIFF({Date for costume return}, {Date for costume collection}, “days”)

This field then multiples with the cost amount in a further field to tally the total cost.

My issue is that when one of the date fields is empty I receive a NaN. And due to this, the total cost amounts cannot be worked out.

Is there a way to adjust the formula for that cell so that if a date isn’t selected, the cell shows as “0”?

This is probably a very easy one to solve but I have been reading for days now and still cannot get my head around how to solve it!

Thanks in advance to anyone who knows. Much appreciated.

[Scott_Ogier]

I just realised that the following formula works for me:

IF(DATETIME_DIFF({Date for prop return}, {Date for prop collection}, “days”),0)

Adding the IF formula replaces the NaN and allows the other cells to function with the mathematic equation.

Thanks

[Justin_Barrett · Accepted solution]

Welcome to the community, @Scott_Ogier! :grinning_face_with_big_eyes: The formula you included in your solution looks incomplete. That will always output a zero if the difference isn’t zero, and nothing if it is zero. I’m guessing you still want the difference to show. This formula is probably closer to what you actually want:

IF(AND({Date for prop return}, {Date for prop collection}), DATETIME_DIFF({Date for prop return}, {Date for prop collection}, "days"),0)

That will return the difference between the listed dates if they both exist, and zero if either of them is missing.

[Scott_Ogier]

Justin - you are phenomenal. Truly. Speedy reply and a can’t thank you enough. That works like a charm. Really appreciate that.