How I Solved This: Reflect a User’s Correct Time Zone in a Formula

Key business problem

I want my customer support managers to be able to see the peak times of the support center to help with scheduling staff.

I can create a quick formula field returning the Hour portion of the Case Created Date, but I need to hard code the offset for the user’s time zone. And, due to daylight savings, some records show as 1 hour before they were actually created, while others show the right time.

Background 

I simply love, love, love answering questions in the community, mostly formula ones.

One day, I came across a query about getting the time value of a date/time field in a formula.

When doing so, the formula always returns the value in GMT. If you’re in another time zone, you can then offset this result (for example, 9 a.m. PDT is 4 p.m. GMT, so you would add 7 hours to your result). You can read more in this Salesforce Help article in the “A Note About Date/Time and Time Zones” section.

I quickly realized that offsetting the result was not enough. I also needed to take Daylight Saving Time (DST) into account. I was lucky enough that this question was for one time zone in Europe only, and since there is a set DST rule in the EU, we could find a formula that worked.

However, I came across other similar questions with users working in international companies who were struggling, because some countries use DST and some don’t. The dates and the time changes are different for every country, and there are even countries that use it in some parts and not others (for example, there are two states in the U.S. that don’t use DST; the other 48 do)!

Another curveball was that the user’s time zone cannot be retrieved via formula.

Pen and paper (and brain) to the rescue, and I came up with the solution below which I hope will help others.

How I solved it

I created a flow to run on the User object as well as a formula on Cases that retrieves the hour portion of the Created Date, according to the logged-in user’s time zone and taking the DST of this same user into account. I then showed it in a simple report and graph:

1. Create a new object

First, create a new Timezone object with the following fields:

• Name: Text
• GMT Offset: Number (3,2)
• Summertime Start - Text (255): FYI only, not needed for any calculation
• Wintertime Start - Text (255): FYI only, not needed for any calculation
• Summertime Start Date: Date
• Wintertime Start Date: Date
• Summertime Start Offset : Number (3,2)
• Wintertime Start Offset: Number (3,2)

• Southern Hemisphere: Checkbox

2. Add fields to the User object

Next, create the following fields on the User object:

• Timezone Name: Text (80)
• GMT Offset: Number (3,2)
• Summertime Start - Text (255): FYI only, not needed for any calculation
• Wintertime Start - Text (255): FYI only, not needed for any calculation
• Summertime Start Date: Date
• Wintertime Start Date: Date
• Summertime Start Offset: Number (3,2)
• Wintertime Start Offset: Number (3,2)
• Southern Hemisphere: Checkbox

Add these fields to the page layout if needed, but I would recommend you set them as read-only with Field-Level Security first.

3. Create a .csv file and upload it to the Timezone object 

This is the hardest part, but worry not! I’ve done the heavy lifting. It took me a while, but I compiled the necessary data in this file; you can simply download it as a .csv file and upload it to the Timezone custom object.

A few things to note:

• I used this site to get the summertime and wintertime offset (with UTC).
• The year in the dates in this file are irrelevant. I chose 1900 but it does not matter. 
• The day in the dates in this file are set to the latest possible day according to the time zone rule. For example:
  • In the U.S., summertime starts on the second Sunday in March. This can be on any day between March 8 and March 14, so I used March 14.
  • In the EU, summertime starts on the last Sunday in March, which can be on any day between the 25th and 31st of March, so I used March 31.
• Since the formula I use (in point 6) takes Sunday as a reference, and since summertime starts on a Friday in Israel, the “Summertime Start Offset” is set to -3 (2 a.m. local time and +2 hours GMT offset and an extra hour for the DST) +48 hours (2 days before Sunday), hence the result of 45.
• Wintertime and summertime dates for the Southern hemisphere are inverted so the formula works.
• Unfortunately, two time zones do not follow the Gregorian calendar for their DST so this solution will not work for them:
  • Iran uses the Persian (or Jalaali) calendar.
  • Morocco uses the Ramadan start and end dates for the start and end to their DST, which follows a lunar calendar. 
• The dates in this file are in the European format (DD/MM/YYYY). You may need to change the format to fit your locale.
• Dates may need to be changed depending on your time zone.
• The “Summertime Start” and “Wintertime Start” columns are not needed as such, but they help understand what the pattern is. They’re here as an FYI only, just like the “Uses DST?” column.

Caveat: The data is correct to the best of my knowledge at the time I am writing this post. However, it could change at any time if new DST laws are passed in any given country, so please make sure you review it before uploading.

4. Create a record-triggered flow

How simple is this?

All this flow does is retrieve the field values in the Timezone object and update the User record with the same values.

Here’s a step-by-step on how to create it:

1. Set the Start element to trigger when “A record is created or updated”, and select Before the record is saved in the Run the Flow section. Select User as the object and set the conditions like so (using {!$GlobalConstant.True} as the value):

This means this flow will only fire when either the new Timezone Name field is not populated (when a new user is created) or a user’s time zone is changed, as we will need to update our custom fields. Since I only need to update the User record, I chose to trigger the flow before save, as it is more efficient.

2. Add a Get Record element to the canvas and set it up like below. This will allow me to retrieve all the fields in the Timezone record where the Name of the record is the same as the name of the User’s time zone:

3. Add an Assignment element as such:

In order to get the Variable, just click in the field, select $Record (User), then select the necessary field:

In order to get the Value, just click in the field, select Timezone from GetTimezone (Timezone__c) in the “RECORD (SINGLE) VARIABLES” section, then select the necessary field:

Variable	Operator	Value
$Record>Timezone_Name__c	Equals	GetTimezone>Name
$Record>GMT_Offset__c	Equals	GetTimezone>GMT_Offset__c
$Record>Southern_Hemisphere__c	Equals	GetTimezone>Southern_Hemisphere__c
$Record>Summertime_Start_Date__c	Equals	GetTimezone>Summertime_Start_Date__c
$Record>Summertime_Start_Offset__c	Equals	GetTimezone>Summertime_Start_Offset__c
$Record>Summertime_Start__c	Equals	GetTimezone>Summertime_Start__c
$Record>Wintertime_start_Date__c	Equals	GetTimezone>Wintertime_Start_Date__c
$Record>Wintertime_Start_Offset__c	Equals	GetTimezone>Wintertime_Start_Offset__c
$Record>Wintertime_start__c	Equals	GetTimezone>Wintertime_start__c

 

4. Save the flow, add a description, and activate it.

5. Update the existing user records

Unfortunately, this solution does not work for existing user records unless you update their time zones. There are a few ways you can update these records, but I personally like automations. 

1. All you need to do is create a schedule-triggered flow that would run once only and would go over every active user record where the fields are blank, and update them accordingly:

2. Filter Conditions (you can add more filters if needed; for example, User Type Equals Standard):

Here, I am only checking if the Timezone Name field on the User record is blank, which means there is no data from the corresponding Timezone record on this User record:

3. Add a Get Record element. Just like before, this will allow me to retrieve all the fields in the Timezone record where the Name of the record is the same as the name of the User’s time zone:

4. Add an Update Record element so we update the custom Timezone fields on the User record:

Field	Operator	Value
GMT_Offset__c	Equals	GetTimezone>GMT_Offset__c
Southern_Hemisphere__c	Equals	GetTimezone>Southern_Hemisphere__c
Summertime_Start_Date__c	Equals	GetTimezone>Summertime_Start_Date__c
Summertime_Start_Offset__c	Equals	GetTimezone>Summertime_Start_Offset__c
Summertime_Start__c	Equals	GetTimezone>Summertime_Start__c
Wintertime_Start_Offset__c	Equals	GetTimezone>Wintertime_Start_Offset__c
Wintertime_start_Date__c	Equals	GetTimezone>Wintertime_Start_Date__c
Wintertime_start__c	Equals	GetTimezone>Wintertime_start__c
Timezone_Name__c	Equals	GetTimezone>Name

 

5. Save, add a description, activate, and let it run.

6. Create a formula field on Cases

We’re finally there! 

This is where the magic happens. You now need to create a formula field on Cases. I simply called it “Created Hour”:

Here’s the complete formula:

IF( OR(
ISBLANK( $User.Summertime_Start_Offset__c ),

CreatedDate< DATETIMEVALUE(DATE(YEAR(DATEVALUE(CreatedDate)),MONTH($User.Summertime_Start_Date__c),DAY($User.Summertime_Start_Date__c))
-(WEEKDAY(DATE(YEAR(DATEVALUE(CreatedDate)),MONTH($User.Summertime_Start_Date__c),DAY($User.Summertime_Start_Date__c)))-1)) + $User.Summertime_Start_Offset__c /24,

CreatedDate>=
DATETIMEVALUE(DATE(YEAR(DATEVALUE(CreatedDate)),MONTH($User.Wintertime_start_Date__c),DAY($User.Wintertime_start_Date__c))
-(WEEKDAY(DATE(YEAR(DATEVALUE(CreatedDate)),MONTH($User.Wintertime_start_Date__c),DAY($User.Wintertime_start_Date__c)))-1))+$User.Wintertime_Start_Offset__c/24

),

HOUR(TIMEVALUE(CreatedDate+$User.GMT_Offset__c /24))
+IF( AND($User.Southern_Hemisphere__c, NOT(ISBLANK( $User.Summertime_Start_Offset__c ))),1,0)
-IF(HOUR(TIMEVALUE(CreatedDate+ $User.GMT_Offset__c /24))
+IF( AND($User.Southern_Hemisphere__c, NOT(ISBLANK( $User.Summertime_Start_Offset__c ))),1,0)>23,24,0)

,
HOUR(TIMEVALUE(CreatedDate+(1+ $User.GMT_Offset__c )/24))
-IF( AND($User.Southern_Hemisphere__c, NOT(ISBLANK( $User.Summertime_Start_Offset__c ))),1,0)
-IF(HOUR(TIMEVALUE(CreatedDate+(1+ $User.GMT_Offset__c )/24))
-IF( AND($User.Southern_Hemisphere__c, NOT(ISBLANK( $User.Summertime_Start_Offset__c ))),1,0)>23,24,0)

)

Let’s try to break it down.

The most complicated part of the formula is:

-(WEEKDAY(DATE(YEAR(DATEVALUE(CreatedDate)),MONTH($User.Summertime_Start_Date__c),DAY($User.Summertime_Start_Date__c)))-1)

This is how I calculate the actual Sunday the time change occurs on the year of the Created Date.

Since the WEEKDAY() function returns a number from 1 for Sunday to 7 for Saturday, and I have the latest possible Sunday summer/winter time on the User record, I can simply take this number and subtract 1 (so Sunday returns 0, Monday returns 1, Tuesday returns 2, etc.), and then subtract this from the highest possible Sunday.

Let’s take Ireland as an example:

1. Summertime starts on the “Last Sunday in March”.
2. The latest possible Sunday when we switch to summertime is on March 31.
3. In 2021, March 31 fell on a Wednesday.
4. The last Sunday in March was the 28th.

The formula will then take March 31, 2021 (latest possible Sunday), and subtract 3 (since WEEKDAY() for Wednesday is 4 minus the extra 1). This returns March 28. 

The formula now reads like so:

If the Summertime Start Offset field is not populated (which means the DST does not apply)

ISBLANK( $User.Summertime_Start_Offset__c )

OR

If the Created Date on the Case record is before the logged-in user’s Summertime start date/time for the year of the Case Created Date

CreatedDate<
DATETIMEVALUE(DATE(YEAR(DATEVALUE(CreatedDate)),MONTH($User.Summertime_Start_Date__c),DAY($User.Summertime_Start_Date__c))
-(WEEKDAY(DATE(YEAR(DATEVALUE(CreatedDate)),MONTH($User.Summertime_Start_Date__c),DAY($User.Summertime_Start_Date__c)))-1)) + $User.Summertime_Start_Offset__c /24

OR

If the Created Date on the Case record is after or equal to the logged-in user’s Wintertime start date/time for the year of the Case Created Date

CreatedDate>= DATETIMEVALUE(DATE(YEAR(DATEVALUE(CreatedDate)),MONTH($User.Wintertime_start_Date__c),DAY($User.Wintertime_start_Date__c))
-(WEEKDAY(DATE(YEAR(DATEVALUE(CreatedDate)),MONTH($User.Wintertime_start_Date__c),DAY($User.Wintertime_start_Date__c)))-1))+$User.Wintertime_Start_Offset__c/24

THEN

Return the Hour of the Case Created Date and offset it according to the logged-in user’s time zone (wintertime hour)

HOUR(TIMEVALUE(CreatedDate+$User.GMT_Offset__c /24))

OTHERWISE

Return the Hour of the Case Created Date and offset it according to the logged-in user’s time zone and add 1 hour (summertime hour).

HOUR(TIMEVALUE(CreatedDate+(1+ $User.GMT_Offset__c )/24))

FOR EACH RESULT:

I have to adjust the time for the Southern Hemisphere (Southern Hemisphere checkbox) if they use DST (Summertime Start Offset field is populated) since I had to invert their summertime and wintertime dates.

+/-IF( AND($User.Southern_Hemisphere__c, NOT(ISBLANK( $User.Summertime_Start_Offset__c ))),1,0)

Finally, I’ve added a check so when the result is more than 23, I subtract 24 to it so this formula can only return whole numbers between 0 and 23.

-IF(HOUR(TIMEVALUE(CreatedDate+ $User.GMT_Offset__c /24))
+IF( AND($User.Southern_Hemisphere__c, NOT(ISBLANK( $User.Summertime_Start_Offset__c ))),1,0)>23,24,0)

7. Create a report on Cases

That’s it! You now have everything you need to create the report. That’s the easy part. Simply create a report on Cases grouped by either the Created Date on rows and Created Hour on column, or only the Created Hour on rows:

Why don’t you add a Dashboard, too!?

Business results

Since I’m in Ireland, I’m on the GMT time zone in the winter and on GMT+1 in the summer. Have a look at these two dashboard components for the same Case records. The first one shows the times in GMT only, whereas the second one shows the times according to DST in Ireland:

If I didn’t have a view according to my time zone with DST, I would think that my contact center starts getting busy at 7 a.m., whereas it does get busy from 8 a.m. I can now schedule my staff accordingly.

Do try this at home

There are many different reasons why you would want to see times according to DST. You can find quite a few questions about DST on the Trailblazer Community:

• Returning the correct time frame
• Schedule a flow
• Show the right appointment time

If all your users are in one time zone:

You can simply create a formula field without having to worry about the flow on Users. Here’s the formula for the GMT time zone in Europe (Ireland, the U.K., and Portugal):

IF( OR(CreatedDate< DATETIMEVALUE(DATE(YEAR(DATEVALUE(CreatedDate)),3,31)-(WEEKDAY(DATE(YEAR(DATEVALUE(CreatedDate)),3,31))-1)) +1/24,
CreatedDate> DATETIMEVALUE(DATE(YEAR(DATEVALUE(CreatedDate)),10,31)- (WEEKDAY(DATE(YEAR(DATEVALUE(CreatedDate)),10,31))-1))+1/24),
HOUR(TIMEVALUE(CreatedDate))
-IF(HOUR(TIMEVALUE(CreatedDate))>23,24,0)
,
HOUR(TIMEVALUE(CreatedDate+1/24))
-IF(HOUR(TIMEVALUE(CreatedDate+1/24))>23,24,0)
)

Here’s the same formula for the New York/Indiana time zones:

IF( OR(
CreatedDate< DATETIMEVALUE(DATE(YEAR(DATEVALUE(CreatedDate)),3,14)
-(WEEKDAY(DATE(YEAR(DATEVALUE(CreatedDate)),3,14))-1))+11/24,
CreatedDate> DATETIMEVALUE(DATE(YEAR(DATEVALUE(CreatedDate)),11,7)
-(WEEKDAY(DATE(YEAR(DATEVALUE(CreatedDate)),11,7))-1))+11/24
),
HOUR(TIMEVALUE(CreatedDate-5/24))
-IF(HOUR(TIMEVALUE(CreatedDate-5/24))>23,24,0)
,
HOUR(TIMEVALUE(CreatedDate-4/24))
-IF(HOUR(TIMEVALUE(CreatedDate-4/24))>23,24,0)
)

Using the formula with a date field:

You can use the same principle with a date field. Here’s a formula for the GMT time zone in Europe returning a date/time at 6 a.m., using TODAY() as the date:

DATETIMEVALUE( TEXT(TODAY())&
IF( OR(NOW()< DATETIMEVALUE(DATE(YEAR(TODAY()),3,31)-(WEEKDAY(DATE(YEAR(TODAY()),3,31))-1) +1/24),
NOW()> DATETIMEVALUE(DATE(YEAR(TODAY()),10,31)- (WEEKDAY(DATE(YEAR(TODAY()),10,31))-1)+1/24))
, " 06:00:00", " 05:00:00"))

Let us know what you thought of this solution, and tell us how you want to use it on Twitter with #AwesomeAdmins #HowISolvedThis.

Resources

• Trailhead Module: Formulas and Validations
• Trailhead Module: Advanced Formulas
• Trailhead Trail: Build Flows with Flow Builder
• Trailhead Project: Import and Export with Data Management Tools

Want to see more good stuff? Subscribe to our channel!

SUBSCRIBE TODAY