DATETIME_PARSE AND CONCATENATE

[Alessandra_Carl]

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!

[kuovonne · Accepted solution]

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]

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!

[kuovonne]

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]

@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 · Accepted solution]

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.