How do I access the user's timezone in a formula field? - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Jason SamsonJason 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 Jason Samson
Jason SamsonJason 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

Jason SamsonJason 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))
Jason SamsonJason 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 
Jason SamsonJason 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

 
Jason SamsonJason 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
Jason SamsonJason Samson
Have you configured your Numeric fields to include at least one decimal point, such as Numeric(17,1) ?
Jason SamsonJason 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!
Leonard WagnerLeonard Wagner
Hi J Samson,

I am using your formula to get the timeoffset in germany. It works perfectly fine in the winter time, but in the summer time it is postponed by one hour.
Can you help me fixing that?
Nathan WeinrichNathan Weinrich
The UserTimezoneOffset solution above does not work for daylight savings.  I got a -5 here indiana, should have been -4.
Jason SamsonJason Samson
The formula can be enhanced to account for Daylight Savings Time by adding the following to the end of the formula above:

+ IF(TODAY() >= DATE(YEAR(TODAY()), 3, 10) && TODAY() < DATE(YEAR(TODAY()), 11, 3), 1, 0)

However, be sure to adjust the start and stop dates in this addition if your country does not start/stop Daylight Savings on the same dates.  The above formula works for US starting at March 10 and stopping at Nov 3rd, hence the 3,10 and 11,3 in the formula.
Nathan WeinrichNathan Weinrich
Yeah, that is exactly my point.  No one wants to or should have to change do such backflips to enter a time properly.
Jason SamsonJason Samson
I fully agree that Salesforce should provide basic Date/Time functions both in GMT and UserTime out of the box.  I will be very happy on the day such workarounds are no longer needed.

In the meantime, if you have a use case that must interpret user local time (e.g. from text-based date/time inputs) but save in GMT, this formula at least makes it possible to do things like this without Apex intervention.  Apex can easily do this because it has GMT and User time methods available.

For reference, the formula with DST adjustment should be:

(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()) +
IF(TODAY() >= DATE(YEAR(TODAY()), 3, 10) && TODAY() < DATE(YEAR(TODAY()), 11, 3), 1, 0)

Save this formula as a Numeric field on the User object and you can refer to their local time offset (in hours) anytime you need it in formulas.
Nathan WeinrichNathan Weinrich
Unfortunately this still does not cover the issue completely.  Specifically, daylight savings time is observed in many different areas and these areas can have different start/end dates for DST.  So the above only considers Indiana.  As well Indiana DST begin and ends at 2am on those days.  So I'm afraid this is way more complex than the above solution.
Marc BourlonMarc Bourlon
Found something that can help. It's not replying to the question exactly, but can certainly be teaked to help.
Flow text template let you display a datetime in the correct timezone. You can then use Flow formulas to extract the time (see discussion here https://success.salesforce.com/_ui/core/chatter/groups/GroupProfilePage?g=0F9300000001omi&fId=0D53A00004f2I8W), or the hours, or the minutes, and conver them to numbers and use them. So, combining Process Builder, Flows, etc. should let you achieve what you want, in a "decently simple" way.
Port CRM DevPort CRM Dev
Jason is this supposed to work with daylight savings as well?
Jeff BergerJeff Berger
For those who are stumbling upon this thread and can solve your requirement with flow, may I recommend this new flow utility from the folks at unofficialsf.com: https://unofficialsf.com/flow-datetime-methods/
A quick install and then in any flow you can take any datetime and apply a the user's timezone via the available flow attribute $User.TimeZoneSidKey!
Doesn't solve for formulas but if you're on this page you might need this in flow as well!