Setting a Timezone on a DATETIME_PARSE of a text field

[Charlie_D_Barkl]

Hi Team,
Soooo, because Airtable doesn’t have a stand alone time field (it really needs one)
I need to create a ridiculous bodge…

I start with a Date field set to local format lets call it {CommDate}

Next I have a bulk standard number field set to integer we’ll call that {Day}

The third field uses DATEADD to add {Day} to {CommDate} easy enough, we’ll call that field {StartDate} it’s set in local format with “use same time zone” unchecked cos I’m in Australia.

Field 4 is a text field with a time of the day written in it in HH:mm format, lets call that field {Time}

Now here’s where it gets tricky…
I CONCATENATE {StartDate} & {Time} it spits out an answer which looks like YYYY-MM-DDT00:00:00.000ZHH:mm
The YYYY-MM-DD matches what’s in {StartDate} and the HH:mm matches the text in {Time} and I can only assume the T00:00:00.000Z is a representation of the UTC/GMT timezone.
Let’s call this field {Date&Time}

But here’s where the problem comes in…
My next field is a DATETIME_PARSE of {Date&Time} with ‘en-au’ as the locale. Once again I’ve got the settings as local format, 24hours and with the “use same time zone” unchecked.
But the formula has naturally assumed that the {Date&Time} field is UTC/GMT and the result the formula spits out is a date and time field but the time is now +10hrs (which is correct for the timezone ‘Australia/Melbourne’) from the text in the {Time} field.
We’ll call this last field {DateTime_Parse} obviously :slightly_smiling_face:

Of course this makes some degree of sense, and I can easily fix it by checking the “use same time zone” box in all the Date & Time fields and my grid view looks perfect…
Except I don’t just use the grid view.
I have a calendar view and that calendar view is synced to my Google Calendar and of course all my records in my Google Calendar are now in UTC/GMT time which would be fine if I was living in Greenwich but I’m not I’m in Melbourne +10 hours away and +11hrs from October through to March every year and all my records (which are scheduled events) are 10 hours later than they should be.
An event starting at 10am is in my Google Calendar at 8pm etc etc.

I’ve tried inserting the SET_TIMEZONE formula in all the various formulas I have running but it doesn’t make a lick of difference.
The {Time} field is deliberately set as a text field to make it convenient to edit
I often need to quickly make a change to the date of multiple records without changing the time hence why I keep the date and time separate at the start and use the CONCATENATE formula to bring them together.

Can anyone help with my problem?
How can I make Airtable recognise that the text in my {Time} field is the ‘Australia/Melbourne’ timezone when it runs it through the DATETIME_PARSE formula?

Cheers,
Charlie

[Charlie_D_Barkl]

I should add that I’ve had success with using;
DATETIME_PARSE((CONCATENATE({Start Date}, {Time})), ‘YYYY-MM-DDT00:00:00.000ZHH:mm’, ‘en-au’)
To do the CONCATENATE and DATETIME_PARSE steps in one field instead of two

I separated them out though to see if I could insert the SET_TIMEZONE formula into either parts of the formula to make it work.

[Justin_Barrett]

What stands out as odd to me is the format that you’re trying to use for parsing:

Charlie_D_Barkle:

DATETIME_PARSE((CONCATENATE({Start Date}, {Time})), ‘YYYY-MM-DDT00:00:00.000ZHH:mm’, ‘en-au’)

You have the year, month, date, time, timezone, and then the time again. It could be that the parser is choking because you’re representing the time in two places: once in the middle—the T00:00:00.000 part—and again at the end—the HH:mm part.

Backing up to something you said earlier:

Charlie_D_Barkle:

I can only assume the T00:00:00.000Z is a representation of the UTC/GMT timezone.

Not quite. Only the Z portion represents the actual timezone. The part before that is the time in hours (24-hour), minutes, seconds, and milliseconds (the “T” just indicates where the time portion begins, separating it from the date portion). To specify a timezone other than GMT, replace “Z” with a numerical value representing the offset in hours and minutes from GMT, prefaced by either + or - depending on the offset direction. For example, if I wanted to indicate my current timezone—Pacific time in the US, which is currently GMT -7, I would replace Z with -07:00. At any rate, the timezone indicator is supposed to be the last part of that particular format. Adding the hours and minutes to the end is likely throwing off the parser.

Anyway, let’s get back to solving your problem. The whole reason that you got that long date/time string in the first place is because you took the output of a date field—which is something called a datetime (a mashup word that simply indicates that both date and time are stored in a single data item—and concatenated it with a string. When doing that, Airtable will always give you that long string ending in Z. The reason that the time values are all set to 0 is because, for a date field that only records a date, the time is automatically set to midnight GMT.

If I’m reading your post correctly, what you really want is to connect the time chosen in the {Time} field with the date chosen in the {Start Date} field. To do that, I suggest replacing the current zeroed-out time with your selected time using the SUBSTITUTE() function, adding your timezone to the end (+10:00), and then parsing that. If done correctly, you wouldn’t need to feed the parser an actual parse string because that layout is one of the formats that it’s designed to read automatically.

I have to step aside and teach a class, but I’ll be back later to show the actual formula you can use to make this work.