Skip to main content

Hello,

I want to calculate the length between a start and an end date. Seems easy, but the result is wrong for the fields that came from a lookup field.

Below a screenshot from my base and my calculations.

Here an explanation:

  • Start Date = automatically set as record is created, formatted as “European”
  • End Date = calculation, depending on how my record ended. This calculation is correct, so nothing wrong here. The format is set to “European” 
    • Date moved to fridge = manually selected from calender
    • End date option 2 = lookup field (original field is date that the record was created)
    • End date option 3 = lookup field (original field is date that the record was created)
  • Total days = wrong calculation:

    • formula: DATETIME_DIFF({End Date}, {Start Date}, 'days')

    • format: number

    • Result is correct for manually selected date in date format

    • Result is wrong or NAN for lookup date

Who can help me fix this issue?

Thank you!

 

screenshot from my base
calculation for my end date, gives correct result in expected format

 

calculation that is giving a wrong result

 

 

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?

 

Hi,
It seems like diff formula considers your end date as M/D/YYYY
Try to temporary set ‘End Date’ format to ISO to check
 

Regarding solution, I think DATETIME_FORMAT transfers Date to String. Then, when DIFF formula get the string from field, it transfers value back to Date, but swaps M/D. Maybe you should do the opposite - use DATETIME_PARSE instead of DATETIME_FORMAT

Hi,
It seems like diff formula considers your end date as M/D/YYYY
Try to temporary set ‘End Date’ format to ISO to check
 

Regarding solution, I think DATETIME_FORMAT transfers Date to String. Then, when DIFF formula get the string from field, it transfers value back to Date, but swaps M/D. Maybe you should do the opposite - use DATETIME_PARSE instead of DATETIME_FORMAT

 

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

Reply


Terms & Conditions