Skip to main content

Setting Timezone without Time in Date Field

  • February 20, 2023
  • 9 replies
  • 292 views
A
Forum|alt.badge.img+6

Hello!

I'm having issues with Airtable's default GMT timezone. I have a Date field (which I do not want to have time displayed for) as well as a formula field using the DATEADD formula to calculate the next time a task is due, based on frequency. 

In the formula field, I can specify timezone. In the Date field, I can't - without adding the time. Because of that, it's calculating incorrectly - everything is off by a day. Only when I add a time do the dates show up correctly. 

Is there any way to fix this? Do I need to add something to my DATEADD formula? 

Thank you!

 

 

 

D

9 replies

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • February 20, 2023

Date only fields are always midnight GMT when used in formulas. Since your formula result is a date, set the formatting tabl of the formula field to use the same Timezone for everyone (GMT). 

C
A
Forum|alt.badge.img+6
  • AnitaHallberg
  • Author
  • New Participant
  • February 22, 2023

Date only fields are always midnight GMT when used in formulas. Since your formula result is a date, set the formatting tabl of the formula field to use the same Timezone for everyone (GMT). 


Thank you for your response! Is there any way to make them both appear in EST?

M
C
L
Forum|alt.badge.img+4
  • lailah11
  • New Participant
  • March 14, 2023

I would also like to know how to set the time zone on a date field without displaying the time. Why is the "Use the same time zone for all collaborators" option not available without the time being displayed? Is there a way to enable it?

M
L
S
M
Forum|alt.badge.img+7
  • Meagan_Caesar
  • Known Participant
  • June 3, 2023

I would also like to know how to set the time zone on a date field without displaying the time. Why is the "Use the same time zone for all collaborators" option not available without the time being displayed? Is there a way to enable it?


I would also like to know this. It's very frustrating!

S
A
L
Forum|alt.badge.img+6
  • Lassi_Seppa
  • Inspiring
  • December 6, 2023

Has anybody figured out this ? My goal is to add "duration" field into "date only" field with formula. But stuck on this timezone problem and formula keeps returning wrong timezone no matter where I adjust timezone with SET_TIMEZONE.

S
Shawn_Speidel
C
A
Forum|alt.badge.img+4
  • Allison_BM
  • New Participant
  • September 11, 2024

I'm having this same issue. Has anyone figured out a solution?

S
L
Forum|alt.badge.img+6
  • Lassi_Seppa
  • Inspiring
  • September 11, 2024

I have made this formula to my situation. I wanted to convert "date only" field to "date+time" using "duration" field. Everything needed convert to string, and back to the date format. Here is my formula, I hope it helps:

IF({📅 Start Day},
  DATEADD(
    DATETIME_PARSE(DATETIME_FORMAT({📅 Start Day}, 'YYYYMMDD-HHmm') & "+" &
      DATETIME_FORMAT(SET_TIMEZONE(DATETIME_PARSE(DATETIME_FORMAT({📅 Start Day}, 'YYYYMMDD-HHmm'), 'YYYYMMDD-HHmm'), 'europe/helsinki'), "HHmm"), 
    'YYYYMMDD-HHmmZZ'), {🕑 Start Time}, 'seconds'
  )
)
C
Forum|alt.badge.img+2

I needed a field that showed the number of days until a scheduled course so that I can send out reminder emails X days in advance.  The problem was that with AirTable using GMT, and me also not wanting Time in my date fields, it was not triggering at the right time. Not quite the same as the OP scenario, but for whatever it’s worth, this was my answer. 

Do DATETIME_DIFF in hours, then add the appropriate hours to get it to midnight my time, then divide by 24 and roundup.

ROUNDUP((DATETIME_DIFF({Course Date},TODAY(),'hours')+3)/24,0)

TheTimeSavingCo
C
Forum|alt.badge.img+2

A little more trial and error and I found that around midnight local time, and also once the date difference would hit 0 (day of the course) that the formula above started behaving erratically.  Fixed it with this formula, which seems to work best overall:

DATETIME_DIFF({Course Date},DATETIME_FORMAT(DATEADD(NOW(), -6, 'hours'),'YYYY-MM-DD'),'days') where the “-6” is your offset from GMT

Terms & ConditionsAccessibility statement