Welcome to another “How I Solved This.” In this series, we do a deep dive into a specific business problem and share how one #AwesomeAdmin chose to solve it. Once you learn how they solved their specific problem, you’ll be inspired to try their solution yourself! Watch how Eric Praud, Senior Business Analyst at Epam PolSource, was able to reflect a logged-in user’s correct time zone in a formula, and then read all the details in the post below.
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)!
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.
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”:
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:
Summertime starts on the “Last Sunday in March”.
The latest possible Sunday when we switch to summertime is on March 31.
In 2021, March 31 fell on a Wednesday.
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
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.
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:
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):
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:
Welcome to another “How I Solved It.” In this series, we do a deep dive into a specific business problem and share how one Awesome Admin chose to solve it. Once you learn how they solved their specific problem, you’ll be inspired to try their solution yourself! Watch how Deanne Walters uses Flow to create […]
Welcome to another “How I Solved It.” In this series, we do a deep dive into a specific business problem and share how one Awesome Admin chose to solve it. Once you learn how they solved their specific problem, you’ll be inspired to try their solution yourself! Watch how Dee Ervin searched unsearchable field data […]
Welcome to another “How I Solved It.” In this series, we do a deep dive into a specific business problem and share how one #AwesomeAdmin chose to solve it. Once you learn how they solved their specific problem, you’ll be inspired to try their solution yourself! Watch how Rachel Park Brayboy used her sewing (and […]