# Timezone offset calculation

**URL:** <https://community.fibery.io/t/timezone-offset-calculation/3239>\
**Category:** API & Programming\
**Created:** [September 2, 2022, 5:31pm UTC](https://community.fibery.io/t/timezone-offset-calculation/3239 "2022-09-02T17:31:56Z")\
**Posts on this page:** 1\
**Showing post:** 6

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [October 12, 2023, 2:06pm UTC](https://community.fibery.io/t/timezone-offset-calculation/3239/6 "2023-10-12T14:06:30Z")

</div>

**UPDATE:** simplified versions can be made using the formulas here: [Summer time formulas (aka DST)](https://community.fibery.io/t/summer-time-formulas-aka-dst/9623)

I have dug into timezones a little, and concluded that it is possible to write a formula that will convert a DateTime value to a Date field based on a specific timezone, taking into account daylight saving.  
In the case of Europe, the formula would be as follows:

```auto
DateTimeField +
  Hours(
    If(
      DateTimeField >=
        Date(Year(DateTimeField), 4, 1) -
          Days(
            If(
              WeekDayName(Date(Year(DateTimeField), 4, 1)) = "Monday",
              1,
              If(
                WeekDayName(Date(Year(DateTimeField), 4, 1)) = "Tuesday",
                2,
                If(
                  WeekDayName(Date(Year(DateTimeField), 4, 1)) = "Wednesday",
                  3,
                  If(
                    WeekDayName(Date(Year(DateTimeField), 4, 1)) = "Thursday",
                    4,
                    If(
                      WeekDayName(Date(Year(DateTimeField), 4, 1)) = "Friday",
                      5,
                      If(
                        WeekDayName(Date(Year(DateTimeField), 4, 1)) =
                          "Saturday",
                        6,
                        7
                      )
                    )
                  )
                )
              )
            )
          ) +
          Hours(1) and
        DateTimeField <
          Date(Year(DateTimeField), 11, 1) -
            Days(
              If(
                WeekDayName(Date(Year(DateTimeField), 11, 1)) = "Monday",
                1,
                If(
                  WeekDayName(Date(Year(DateTimeField), 11, 1)) = "Tuesday",
                  2,
                  If(
                    WeekDayName(Date(Year(DateTimeField), 11, 1)) =
                      "Wednesday",
                    3,
                    If(
                      WeekDayName(Date(Year(DateTimeField), 11, 1)) =
                        "Thursday",
                      4,
                      If(
                        WeekDayName(Date(Year(DateTimeField), 11, 1)) =
                          "Friday",
                        5,
                        If(
                          WeekDayName(Date(Year(DateTimeField), 11, 1)) =
                            "Saturday",
                          6,
                          7
                        )
                      )
                    )
                  )
                )
              )
            ) +
            Hours(1),
      2,
      1
    )
  )

```

where the last two numbers represent the offset in hours for summer and winter time respectively.

I haven’t exhaustively checked that it works correctly for every possible date, so please let me know if you find any bugs.

If you’re in North America, the formula is slightly different (since the dates when the clocks change is different and the time of day for the change is based on local time and not UTC):

```auto
DateTimeField -
  Hours(
    If(
      DateTimeField >=
        Date(Year(DateTimeField), 3, 1) +
          Days(
            If(
              WeekDayName(Date(Year(DateTimeField), 3, 1)) = "Monday",
              13,
              If(
                WeekDayName(Date(Year(DateTimeField), 3, 1)) = "Tuesday",
                12,
                If(
                  WeekDayName(Date(Year(DateTimeField), 3, 1)) = "Wednesday",
                  11,
                  If(
                    WeekDayName(Date(Year(DateTimeField), 3, 1)) = "Thursday",
                    10,
                    If(
                      WeekDayName(Date(Year(DateTimeField), 3, 1)) = "Friday",
                      9,
                      If(
                        WeekDayName(Date(Year(DateTimeField), 3, 1)) =
                          "Saturday",
                        8,
                        7
                      )
                    )
                  )
                )
              )
            )
          ) +
          Hours(Delta) and
        DateTimeField <
          Date(Year(DateTimeField), 11, 1) +
            Days(
              If(
                WeekDayName(Date(Year(DateTimeField), 11, 1)) = "Monday",
                6,
                If(
                  WeekDayName(Date(Year(DateTimeField), 11, 1)) = "Tuesday",
                  5,
                  If(
                    WeekDayName(Date(Year(DateTimeField), 11, 1)) =
                      "Wednesday",
                    4,
                    If(
                      WeekDayName(Date(Year(DateTimeField), 11, 1)) =
                        "Thursday",
                      3,
                      If(
                        WeekDayName(Date(Year(DateTimeField), 11, 1)) =
                          "Friday",
                        2,
                        If(
                          WeekDayName(Date(Year(DateTimeField), 11, 1)) =
                            "Saturday",
                          1,
                          0
                        )
                      )
                    )
                  )
                )
              )
            ) +
            Hours(Delta + 1),
      Delta - 1,
      Delta
    )
  )

```

In this case, you will need to replace `Delta` with a number representing the number of hours behind UTC you are in the winter period. So for example, I think in New York it would be 5, and in Los Angeles it would be 8.

Please let me know what you think.

---

_[View the full topic](https://community.fibery.io/t/timezone-offset-calculation/3239)._