Help

Parse a date while keeping the European date format

Topic Labels: Dates & Timezones
Solved
Jump to Solution
6093 10
cancel
Showing results for 
Search instead for 
Did you mean: 
James_Samuel
6 - Interface Innovator
6 - Interface Innovator

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.

Formula Issue1.png

Formula Issue2.png

Formula Issue3.png

1 Solution

Accepted Solutions
Alexander_Sorok
6 - Interface Innovator
6 - Interface Innovator

Hi Tuur,

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

Alex

See Solution in Thread

10 Replies 10

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:

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

James_Samuel
6 - Interface Innovator
6 - Interface Innovator

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!

Screen Shot 2016-10-07 at 7.03.58 AM.png

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!

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

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

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

Tuur
10 - Mercury
10 - Mercury

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

Alexander_Sorok
6 - Interface Innovator
6 - Interface Innovator

Hi Tuur,

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

Alex

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?

Tuur
10 - Mercury
10 - Mercury

Yup.

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

Works.

Cheers @Alexander_Sorokin