Help

NAN with DATETIME_DIFF (Help !)

Topic Labels: Formulas
1396 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Mario_Villalobo
5 - Automation Enthusiast
5 - Automation Enthusiast

When calculating the difference between two dates it gives me NAN, since one of the cells is empty.

¿How can i fix it? Help me :blush:

IF(
AND(
{start_isolete},
NOT(DATETIME_DIFF({date_out}, {start_isolete}, ‘days’) < 1)
),
DATETIME_DIFF({date_out}, {start_isolete}, ‘days’)
)

1 Reply 1

Welcome to the community, @Mario_Villalobos1! :grinning_face_with_big_eyes: Add the other date field into the initial AND() test. That will ensure that the difference is only calculated if both dates exist.

IF(
  AND(
    {start_isolete},
    {date_out},
    NOT(DATETIME_DIFF({date_out}, {start_isolete}, ‘days’) < 1)
  ),
  DATETIME_DIFF({date_out}, {start_isolete}, ‘days’)
)

With many programming languages, the and operator (often represented as &&) will test each expression in order, and will return false once it encounters an expression that returns false (or false-equivalent). This can be useful because it means that not all of the expressions have to be tested in order to get a true/false result. I’m not sure if Airtable’s AND() function operates in the same way, but my gut says that it probably does, which would mean that it would return false to the surrounding IF() function the moment it finds a blank date field, and not even bother testing to see if the difference is less than 1.