Help

Date formatting

Solved
Jump to Solution
1062 5
cancel
Showing results for 
Search instead for 
Did you mean: 
alternaz
6 - Interface Innovator
6 - Interface Innovator

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) :

Capture d’écran 2022-12-03 à 15.35.18.png

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

Capture d’écran 2022-12-03 à 15.42.15.png

Date on the template :

Capture d’écran 2022-12-03 à 15.38.57.png

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

Thank you in advance!

1 Solution

Accepted Solutions
alternaz
6 - Interface Innovator
6 - Interface Innovator

I found a solution few days ago with :

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

Thanks a lot @Justin_Barrett & @kuovonne !

See Solution in Thread

5 Replies 5

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.

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?

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
6 - Interface Innovator
6 - Interface Innovator

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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.