Date formatting

[alternaz]

Hello,

I configured my date format to European on Airtable, however, when I send this data to Integromat and making a Google Sheet template, it reformats the date in US.

Date on Airtable (03/12/2022) :

Date format received by Integromat (2022-12-05, yes I know is not the same date :p) :

Date on the template :

Do you have any idea why the date format changes on export?
Do you know any way to circumvent this?

Thank you in advance!

[kuovonne]

The date formatting in Airtable is for display purposes only. It does not affect how data is sent to Integromat. Under the hood, Airtable dates are stored as ISO strings.

Some options to try…

Use an Airtable formula field to format the date as a string with DATEtIME_FORMAT()

Have Integromat parse and format the date.

Set the date formatting options in your spreadsheet.

[alternaz]

Thanks for your answer but I think i'm a little bit to new on the formula's world to understand that. I found on the forum this solution :

DATETIME_FORMAT({Date du jour},‘DD-MM-YYYY’))

But I can't close apply the change, I have an error.

Have you some idea?

[Justin_Barrett]

Make sure that the quotes around your date format string are non-styled quotes (i.e. not "curly"). In your comment above, the quotes are styled, which is the default format if the formula isn't specifically formatted with the forum editor. The easiest way to check is to manually replace each quote and see if the error remains.

[alternaz · Accepted solution]

I found a solution few days ago with :

DATETIME_FORMAT(
  SET_TIMEZONE({AutoDateOfTheDayField}, 'Europe/Paris'), 
  'D/M/YYYY'
)

Thanks a lot @Justin_Barrett & @kuovonne !

[Benoit_Dhennin]

Thanks for this ! I've tried to use it in a DATEDIFF formula field, but the results are wrong...

I have 2 date fields : {Start} & {End}

When I use DATEDIFF({End}, {Start}) I have wrong results. I guess the problem is because I'm in europe and my date fields are in a european format.

I've tried : 

DATETIME_DIFF(
  SET_TIMEZONE(
    DATETIME_FORMAT({Date de fin}, 'YYYY-MM-DDTHH:mm:ssZ'),
    'Europe/Paris'
  ),
  SET_TIMEZONE(
    DATETIME_FORMAT({Date de Début}, 'YYYY-MM-DDTHH:mm:ssZ'),
    'Europe/Paris'
  ),
  'hours'
)

But the results are still wrong, see :

18/9/2023 00:00 - 18/5/2024 23:59 = 1:38

18/9/2023 11:40 - 22/9/2023 11:40 = 0:02

2/10/2023 16:30 - 2/10/2023 17:30 = 0:00

Actually all the duration on between 2 dates where only the hours are differents are equal to 0.

I'd appreciate your help, thanks and have a great day.