Skip to main content
Topics#Data ManagementJason Samson's Question
Jason Samson (Advanced Bionics) asked in #Data Management
May 28, 2016, 12:35 AM

How do I access the user's timezone in a formula field?

I have searched high and low for the answer to this question and was never able to find a solution.  The Salesforce documented answer is that formulas always calculate in GMT, and the user's timezone is simply not available.

 

There are scenarios when knowing the user's timezone can be very useful in a formula.  For example, when comparing DateTime fields to fixed time values (e.g. TEXT(Today()) + " 08:00:00" could be compared as start of business day).  The DATETIMEVALUE function converts fixed time "literals" into DateTime values, however, it interprets the literal as if it was already in GMT time, rather than as if it was entered in the user's timezone.  This would be easily remedied by adding/subtracting the timezone offset for the user, however, this timezone offset info is not available in formulas.

 

I found a way to calculate the user's timezone offset with a formula, so I am posting the answer to my own question here in case others encounter the same limitation and need a workaround.

 

 
24 answers
  1. Jason Samson (Advanced Bionics)
    May 28, 2016, 12:36 AM
    The solution takes advantage of a confusing inconsistency between two out of the box Salesforce functions, namely DATEVALUE and DATETIMEVALUE.

     

    The first evaluates under the user's timezone, while the latter evaluates as GMT.  We can take advantage of this inconsistency and derive the user's timezone as follows.  Paste this code into a new numeric formula field named "UserTimezoneOffset":

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 00:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 01:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 02:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 03:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 04:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 05:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 06:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 07:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 08:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 09:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 10:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 11:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 12:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 13:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 14:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 15:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 16:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 17:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 18:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 19:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 20:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 21:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 22:00:00")) - TODAY()) + 

     

    (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 23:00:00")) - TODAY())

     

    This formula works because for every hour offset from GMT, the answer for "what day is today?" differs by one.  In other words, the DATEVALUE of any literal hour of the day interpreted as GMT will be a day off from the user's TODAY() based on their timezone setting on their User record.  Adding together each of these discrepencies hour by hour ends up yielding the same answer as their timezone offset.

     

     
Write an answer...
0/9000