Timezone offset calculation

API & Programming
6

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:

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):

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.

4 Likes