Ask Search:
J SamsonJ Samson 

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.

 
Best Answer chosen by J Samson
J SamsonJ Samson
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.

 

All Answers

J SamsonJ Samson
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.

 
This was selected as the best answer
Michaela ChrysostomouMichaela Chrysostomou
I am interested in the above logic. How can I use it so when my users add in a date/time field a value then to convert in GMT(0) whatever the timezone of the user is. if the user add 11:00 (GMT+3) to convert in 11:00(GMT(0))
J SamsonJ Samson
DateTime fields always store values in GMT+0 time, but are entered by users and displayed to users in their own time zone.  So if a user is setup as GMT+3 time zone under user settings, they should be able to enter a datetime value (e.g. 6/12/2016 11:00), and the system will automatically convert and save this as "6/12/2016 14:00", automatically converting from GMT+3 to GMT(+0).  Since this conversion is automatic, most situations would not require the formula above.

One time where the formula is useful is when there is a need to compare a literal time to a DateTime field value, or NOW().  For instance, how much time is between 8:00 am (in the user's time zone) and some other DateTime field value (e.g. MyDateTimeValue)?  The typical way to calculate this in a formula would be to use [ MyDateTimeField - DATETIMEVALUE(TEXT(TODAY()+" 8:00")) ].  However, this formula would not be comparing apples to apples if the user's timezone is anything other than GMT(+0), since the DATETIMEVALUE function always assumes the literal time is already entered in GMT.  So to adjust the literal time to match the user's time zone, we must add the time zone of the user to the value from the DATETIMEVALUE function.  In other words, if you have a formula field named "UserTimezoneOffset" containing the formula above, then simply add this to the literal in order to convert it, like this:

MyDateTimeField - ( DATETIMEVALUE(TEXT(TODAY()+" 8:00")) - UserTimezoneOffset / 24 )
Michaela ChrysostomouMichaela Chrysostomou
I want my formula to return a date/time field. I receive error User-added image 
J SamsonJ Samson
My instructions above state "Paste this code into a new numeric formula field".  It returns a number, not a DateTime.  If you want a datetime, you need to add the offset to a DateTime value (e.g. Now() or any other DateTime field), but remember to the number divide by 24, because this formula returns the offset in hours not days, and when adjusting DateTime, the units are always in days.  See the example in my last post above for how to use this numeric formula field (UserTimezoneOffset) within another formula in order to dynamically adjust a DateTime value based on the user's time zone.
Kalai ArumugamKalai Arumugam
Great, this really help me. However, I facing an issue. There is a 30mins difference for users in IST. I'm using the same formula displayed here. Anything you can help me here?

User-added image

 
J SamsonJ Samson
If you must support time zones that have 30 minute offsets, the only answer is to duplicate each line of code above with another line at the 30 minute increment between each hour.  Then you must also multiply all lines (including whole and half hour increments) by 0.5 so that each line counts for 1/2 hour offset only.  This will result in a much formula twice as large as my example above.

I.e.
0.5*(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 00:00:00")) - TODAY()) + 
0.5*(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 00:30:00")) - TODAY()) + 
0.5*(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 01:00:00")) - TODAY()) + 
0.5*(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 01:30:00")) - TODAY()) + 
Etc.

These are long formulas, so it may be necessary to cache the answer into a static numeric field using a workflow rule with a field update.  This way, if you use the answer from this formula field in other formulas, it will hopefully not exceed the Salesforce compiled formula limit of 5,000 bytes.

Hope this helps!
 
Revathy PrasanthRevathy Prasanth
Thanks Samson for the idea.

I have a similar requirement, where the user should be shown Datetime with 8am TODAY for independant of the timezone.
I created a custom formula field which works well with he hourly offset(UserTimezoneOffset) formula.
DATETIMEVALUE(TEXT(TODAY()) & ' ' & TEXT(8- $User.UserTimezoneOffset__c ) & ':00:00')

But when the formula is updated for 30 minutes, and i created workflow and field update, it is giving unexpected results. 
0.5*(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 00:00:00")) - TODAY()) + 
0.5*(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 00:30:00")) - TODAY()) + 
0.5*(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 01:00:00")) - TODAY()) + 
0.5*(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 01:30:00")) - TODAY()) + 
Etc.

Is there something we have to manipulate on the minute part of time, like with the hour part which we customized?
Could you please help?

Thanks,
Revathy
J SamsonJ Samson
Have you configured your Numeric fields to include at least one decimal point, such as Numeric(17,1) ?
J SamsonJ Samson
Hi Revathy, I just realized the latter part of your question asked about handling the literal minutes part of your own formula field. Yes, if your UserTimezoneOffset formula is decimal (17, 1) and can return half hour increments, then your usage of this formula field in your own formula would need to handle the half hour increment scenario using conditional logic.  There are several ways you could go about this logically, such as embedding IF() function that detects if the text(offset) ends in ".5", and if so, returns "30", else returns "00" into the minutes position. For example, try something like this in the latter part of your formula:

TEXT(8- $User.UserTimezoneOffset__c) & IF(RIGHT(TEXT($User.UserTimezoneOffset__c), 2) = '.5', ':30:00', ':00:00'))

Note: I did not test this so syntax may not be perfect, but hopefully this idea gets you on the right track.
 
Lawson TeoLawson Teo
Thanks for this! I was getting quite frustrated trying to workaround the timezone issue till I came across this!
Abram Vixen ReyesAbram Vixen Reyes
Hi J Samson. Does your solution supports users in DST? Thanks!