Help

DATETIME_PARSE AND CONCATENATE

Topic Labels: Dates & Timezones
Solved
Jump to Solution
3342 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Alessandra_Carl
6 - Interface Innovator
6 - Interface Innovator

 

Hello everyone,

I have to create a calendar of course sessions and I have a table in which I enter in one column the date, in one column the start time and in another column the end time because different days might have different times.

I use the formula DATETIME_PARSE(CONCATENATE(DATESTR(Date),' ',{Start Time})) to create a date & time that can be displayed on the calendar. However, the result of the formula returns the wrong time, increased by one hour.

Am I doing something wrong or is it a bug?

Thanks for any assistance!

2 Solutions

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

This is probably a timezone issue. Are you one hour away from GMT?

You can specify a timezone offset when you use DATETIME_PARSE(). Note however, that you are in an area that switches between daylight savings time and standard time, you will not have a constant timezone offset, and this will make things more difficult.

For example ...
DATETIME_PARSE(
  CONCATENATE(
    DATESTR({Date}),
    " ",
    {Start Time},
    " +01:00"
  ),
  "YYYY-MM-DD H:mm Z"
)

See Solution in Thread

kuovonne
18 - Pluto
18 - Pluto

Thank you for looking up the thread on timezones. I played around a bit more. Note that I am in the USA in "Central" time. Your formula will be slightly different since you use a 24 hour clock instead of am/pm, and you are in a different timezone.

kuovonne_0-1674688933612.png

kuovonne_1-1674689184586.png

 

 

See Solution in Thread

5 Replies 5
kuovonne
18 - Pluto
18 - Pluto

This is probably a timezone issue. Are you one hour away from GMT?

You can specify a timezone offset when you use DATETIME_PARSE(). Note however, that you are in an area that switches between daylight savings time and standard time, you will not have a constant timezone offset, and this will make things more difficult.

For example ...
DATETIME_PARSE(
  CONCATENATE(
    DATESTR({Date}),
    " ",
    {Start Time},
    " +01:00"
  ),
  "YYYY-MM-DD H:mm Z"
)

Alessandra_Carl
6 - Interface Innovator
6 - Interface Innovator

Thank you for the response.
I am in Italy so the time is UTC +1 and it changes to daylight saving time in summer (UTC +2).
Would the solution you proposed change automatically with daylight saving time or would I have to change the formula?
Thank you for your patience!

No, this formula will not change with daylight savings time. It is a hardcoded timezone offset. Changing the formula at different times of the year also will not help. You would need to have something that can tell based on the date if it is daylight savings time or not.

I vaguely recall either Justin or I wrote a very complex formula to detect the actual timezone offset for a specific date at a specific timezone. However the search on this forum isn't very robust since the platform change and I am not currently able to find that post.

Alessandra_Carl
6 - Interface Innovator
6 - Interface Innovator

@kuovonne maybe this is the post you are looking for
https://community.airtable.com/t5/other-questions/set-timezone-and-other-time-related-quirks/td-p/13...

I am trying to understand step by step

 

kuovonne
18 - Pluto
18 - Pluto

Thank you for looking up the thread on timezones. I played around a bit more. Note that I am in the USA in "Central" time. Your formula will be slightly different since you use a 24 hour clock instead of am/pm, and you are in a different timezone.

kuovonne_0-1674688933612.png

kuovonne_1-1674689184586.png