Datetime_diff wrong result on lookup date

Hmm could you try setting your lookup field formatting to use the same timezone, and set the timezone you want?

For the NaN stuff, I think you’ll need to get your formula to check that both the Start and End date exist before doing the DATETIME_DIFF:

IF(
  AND(
    Start,
    End
  ),
  DATETIME_DIFF(
    End,
    Start,
    'days'
  )
)

Thanks for your suggestion.

I have done exactly as you said;

• set all time zones to European and ticked “Use time zone for all collaborators”
• added your If formula to prevent NAN.

Sadly both have no influence on the wrong result. I still get the same result, and also a NaN where both Start and End date are available (see last row of my data).

Anything else you can suggest?

Wonderful, this works!

I solved it by putting  DATETIME_PARSE before DATETIME_FORMAT, as it didn’t work on the IF function returning a date only.

Thanks a lot!

The incorrect `DATETIME_DIFF` results with lookup date fields are likely due to Airtable not recognizing them as true date objects. To fix this, use the `DATETIME_PARSE()` function to explicitly convert the lookup fields ("End Date Option 2" and "End Date Option 3") into datetime objects within your formula, ensuring the StuffYourKindledDay format string in `DATETIME_PARSE()` accurately matches the format of the data in the lookup field. For example: `DATETIME_DIFF(DATETIME_PARSE({End Date Option 2}, 'YYYY-MM-DD HH:mm:ss'), {Start Date}, 'days')`. Experiment with different format strings if the initial one doesn't work, and observe the raw output of the lookup field in a formula to understand its underlying format.