Converting ISO date to European format date with l...

[James_Samuel]

I’m struggling with dates, and suspect it might have to do with the different conventions for describing dates. Here in NZ (and in most of Europe) we use DD-MM-YYYY whereas in the US you use MM-DD-YYYY.

Rather than describe in words, it will be easier to show you these screenshots that tell the story.

I’m trying to present a date (that is recognised as a date), that is stripped of the time details in the DateTimeStart field.

[Julian_Kirkness]

It looks to me like it’s an issue with time zones - for me in the UK the last DATETIME_FORMAT works correctly for all times of the day - but then I am in the UK on the date line!

There is a support article about this though:

Airtable - Support

Formulas and date fields

If you would like to incorporate the data from a date-type field into a formula field, the best way to do this is by using the DATETIME_FORMAT function. (For more information on the different funct...

I hope this helps - it looks as though they have thought about it!

[James_Samuel]

Thanks Julian, yes it seems the problem lies in Airtable assuming I’m in London :stuck_out_tongue:
I wonder how I can tell them where I am so when I enter a date and time it is saved correctly!

[Julian_Kirkness]

Hi James

I’ve had a bit of a play around with this and the Set_Timezone function doesn’t seem to work as expected - I think this is one for Airtable to answer!

[James_Samuel]

Cheers Julian - nice getting to know you - I hope I can return the favour one day :slightly_smiling_face:

[Julian_Kirkness]

I’m sure you will get the chance!

You may be interested - I have literally just now created a new Linked in Group called Mobile and Web Databases which is intended to be a forum for people using services like Airtable (and others) as well as Zapier etc. I’m hoping that it eventually becomes a great place to share experience across different platforms, find reviews etc.

Your welcome to join (as is anyone else reading this):

https://www.linkedin.com/groups/8572976

[James_Samuel]

Still working this problem through with Victoria at Airtable - when we get a resolution I’ll let you know.

[Tuur]

Hey guys! Is this an official bug? It looks like SET_TIMEZONE doesn’t do anything…

[Alexander_Sorok · Accepted solution]

Hi Tuur,

Try DATETIME_FORMAT(SET_TIMEZONE({Some Date Field}, ‘YourTimezone’), ‘HH:mm’))

Alex

[Tuur]

It’s a bit strange. When I use something like:

DATETIME_DIFF(DATETIME_FORMAT(SET_TIMEZONE({Value}, ‘Europe/Amsterdam’), ‘YYYY-MM-DD HH:mm’), NOW(), ‘minutes’)

I get a 240 minute difference when my own value is now…

DATETIME_FORMAT(SET_TIMEZONE(Value, ‘Europe/Amsterdam’), ‘YYYY-MM-DD HH:mm’)

Gives me a timestamp that’s 2 hours later than my original value.

Airtable takes GMT by default or something?