Help

Timezone doesn't work

Topic Labels: Dates & Timezones
Solved
Jump to Solution
1635 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Lisa_Morgan
4 - Data Explorer
4 - Data Explorer

DATETIME_FORMAT(SET_TIMEZONE(Date, 'Australia/Brisbane'), 'DD/MM/YYYY')

This is not working for me. It doesn’t matter what I set the timezone to, it keeps saying it is yesterday. I need help to fix this because it is driving me crazy.

1 Solution

Accepted Solutions

Hi everyone!

Erin with Airtable support here. I was able to help Ignacio with this over email, but I wanted to share the solution here in case others run into the same problem.

The issue is that times are being converted to another timezone and then displayed in 12-hour format, when we want them to be displayed in 24-hour format (and 24-hour format is specified in the formatting settings of the formula field).

Here’s the formula that’s causing this issue:

There’s a tiny bug in this formula that’s rather hard to spot: we’re using hh in the format specifier when we want to be using HH (see this article for all available DATETIME_FORMAT specifiers). hh formats the time in 12-hour format, while HH formats the time in 24-hour format. So even if “24-hour format” is specified in this formula field’s display settings, the times will display as 12-hour times since that’s how they are being formatted and then parsed.

If we change the formula to use HH instead, the times will display properly in 24-hour format. Here’s the final formula that will make these times display as intended:

DATETIME_PARSE(DATETIME_FORMAT(SET_TIMEZONE(Solicitado,'America/Argentina/Buenos_Aires'),'DD/M/YY HH:mm'),'DD/M/YY HH:mm')

I hope someone else finds this information useful! :grinning_face_with_smiling_eyes:

See Solution in Thread

4 Replies 4

In your Date field, try checking the option that says “Use the same time zone for all users”.

In addition to taking Jerremy’s suggestion, you could try showing the time as well to see what is going on. Timezone issues often only have date/times off by a few hours, not days. Once you have things sorted out, then you can remove the time from the formula.

Ignacio_Arrieta
4 - Data Explorer
4 - Data Explorer

Same here, and I have proof.

SOLICITADO column has a ‘CREATED_TIME()’ Formula.
AJUSTE HORARIO has this formula:

DATETIME_PARSE(DATETIME_FORMAT(SET_TIMEZONE(Solicitado,‘America/Argentina/Buenos_Aires’),‘DD/M/YY hh:mm’),‘DD/M/YY hh:mm’)

Results are in the image, and are just… random!!!
Why 16:03 is turned into 01:03?? It should be 13:03!!
This is a BUG! I need my money back Mr. Airtable :grinning_face_with_sweat:
But seriously, we are working with this program, I really need it fixed ASAP.

Captura de pantalla 2020-07-01 a la(s) 18.20.35

HELP @Airtable_Clerk

Hi everyone!

Erin with Airtable support here. I was able to help Ignacio with this over email, but I wanted to share the solution here in case others run into the same problem.

The issue is that times are being converted to another timezone and then displayed in 12-hour format, when we want them to be displayed in 24-hour format (and 24-hour format is specified in the formatting settings of the formula field).

Here’s the formula that’s causing this issue:

There’s a tiny bug in this formula that’s rather hard to spot: we’re using hh in the format specifier when we want to be using HH (see this article for all available DATETIME_FORMAT specifiers). hh formats the time in 12-hour format, while HH formats the time in 24-hour format. So even if “24-hour format” is specified in this formula field’s display settings, the times will display as 12-hour times since that’s how they are being formatted and then parsed.

If we change the formula to use HH instead, the times will display properly in 24-hour format. Here’s the final formula that will make these times display as intended:

DATETIME_PARSE(DATETIME_FORMAT(SET_TIMEZONE(Solicitado,'America/Argentina/Buenos_Aires'),'DD/M/YY HH:mm'),'DD/M/YY HH:mm')

I hope someone else finds this information useful! :grinning_face_with_smiling_eyes: