A common complaint is that Start/End Times relating to Project Activity Appointments appear in GMT/UST (Greenwich Meantime/ Universal Standard Time) when generated via merge field in Documents or Email Templates, rather than the correct Start/End times based on the Time Zone of the Org wide setting or User Time Zone setting.
A workaround to this is to create custom formula fields to capture the Start and End times of the Project Activity Appointment, and use a formula to re-calibrate the times from GMT to the correct time zone. Once created, use these new merge fields in Documents and Email Templates to generate the correct time.
First, you'll need to determine how many hours (or time zones) your time zone is from GMT. For Eastern Time, part of the year is 4 time zones removed from GMT, while the other portion of the year is 5 hours/time zones from GMT. So, we will need to use "4/24" in the calculation for part of the year and "5/24" the other portion of the year.
Note: This Article has been updated to include the Formulas to correctly display the Time when DST (Daylight Savings Time) occurs as well as during Standard Time. THIS FORMULA DOES NOT WORK FOR MULTI-TIME ZONE SYSTEMS. See also, Time Zones - Record Owner on Project Activity
More information on how to calculate the correct decimal value: Date or Time calculations in Formula fields, Workflow field updates and validation rules
Start Time Formula
- Navigate to Setup > Objects > Project Activity > Create New Custom Field
- Field Type: Formula
- Formula Return Type: Text
- Field Label: Start Time
This Formula is for Pacific time zone:
IF ( DATEVALUE(i360__Start__c) >=
DATE ( YEAR (DATEVALUE(i360__Start__c)),3,1)
+
(
14-
CASE( MOD(DATE ( YEAR (DATEVALUE(i360__Start__c)),3,1) - DATE (1900,1,7),7) ,
0,7,MOD(DATE ( YEAR (DATEVALUE(i360__Start__c)),3,1) - DATE (1900,1,7),7))
)
&&
DATEVALUE(i360__Start__c) <
DATE ( YEAR (DATEVALUE(i360__Start__c)),11,1)
+
(
7-
CASE( MOD(DATE ( YEAR (DATEVALUE(i360__Start__c)),11,1) - DATE (1900,1,7),7) ,
0,7,MOD(DATE ( YEAR (DATEVALUE(i360__Start__c)),11,1) - DATE (1900,1,7),7))
),
IF(
OR(
VALUE( MID( TEXT( i360__Start__c - (7/24)), 12, 2 ) ) = 0,
VALUE( MID( TEXT(i360__Start__c - (7/24)), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT(i360__Start__c - (7/24)), 12, 2 ) )
-
IF(
VALUE( MID( TEXT(i360__Start__c - (7/24)), 12, 2 ) ) < 12,
0,
12
)
)
)&
":" &
MID( TEXT( i360__Start__c - (7/24)), 15, 2 )
& " " &
IF(
VALUE( MID( TEXT( i360__Start__c - (7/24)), 12, 2 ) ) < 12,
"AM",
"PM"
),
IF(
OR(
VALUE( MID( TEXT( i360__Start__c - (8/24)), 12, 2 ) ) = 0,
VALUE( MID( TEXT(i360__Start__c - (8/24)), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT(i360__Start__c - (8/24)), 12, 2 ) )
-
IF(
VALUE( MID( TEXT(i360__Start__c - (8/24)), 12, 2 ) ) < 12,
0,
12
)
)
)&
":" &
MID( TEXT( i360__Start__c - (8/24)), 15, 2 )
& " " &
IF(
VALUE( MID( TEXT( i360__Start__c - (8/24)), 12, 2 ) ) < 12,
"AM",
"PM"
))
End Time Formula
- Navigate to Setup > Objects > Project Activity > Create New Custom Field
- Field Type: Formula
- Formula Return Type: Text
- Field Label: End Time
More information on how to calculate the correct decimal value: Date or Time calculations in Formula fields, Workflow field updates and validation rules
This Formula is for Pacific time zone:
IF ( DATEVALUE(i360__End__c) >=
DATE ( YEAR (DATEVALUE(i360__End__c)),3,1)
+
(
14-
CASE( MOD(DATE ( YEAR (DATEVALUE(i360__End__c)),3,1) - DATE (1900,1,7),7) ,
0,7,MOD(DATE ( YEAR (DATEVALUE(i360__End__c)),3,1) - DATE (1900,1,7),7))
)
&&
DATEVALUE(i360__End__c) <
DATE ( YEAR (DATEVALUE(i360__End__c)),11,1)
+
(
7-
CASE( MOD(DATE ( YEAR (DATEVALUE(i360__End__c)),11,1) - DATE (1900,1,7),7) ,
0,7,MOD(DATE ( YEAR (DATEVALUE(i360__End__c)),11,1) - DATE (1900,1,7),7))
),
IF(
OR(
VALUE( MID( TEXT( i360__End__c - (7/24)), 12, 2 ) ) = 0,
VALUE( MID( TEXT(i360__End__c - (7/24)), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT(i360__End__c - (7/24)), 12, 2 ) )
-
IF(
VALUE( MID( TEXT(i360__End__c - (7/24)), 12, 2 ) ) < 12,
0,
12
)
)
)&
":" &
MID( TEXT( i360__End__c - (7/24)), 15, 2 )
& " " &
IF(
VALUE( MID( TEXT( i360__End__c - (7/24)), 12, 2 ) ) < 12,
"AM",
"PM"
),
IF(
OR(
VALUE( MID( TEXT( i360__End__c - (8/24)), 12, 2 ) ) = 0,
VALUE( MID( TEXT(i360__End__c - (8/24)), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT(i360__End__c - (8/24)), 12, 2 ) )
-
IF(
VALUE( MID( TEXT(i360__End__c - (8/24)), 12, 2 ) ) < 12,
0,
12
)
)
)&
":" &
MID( TEXT( i360__End__c - (8/24)), 15, 2 )
& " " &
IF(
VALUE( MID( TEXT( i360__End__c - (8/24)), 12, 2 ) ) < 12,
"AM",
"PM"
))
Resources
Below you will find the Start formula formatting for Eastern Time and Central Time Zones.
Eastern Time Zone:
IF ( DATEVALUE(i360__Start__c) >=
DATE ( YEAR (DATEVALUE(i360__Start__c)),3,1)
+
(
14-
CASE( MOD(DATE ( YEAR (DATEVALUE(i360__Start__c)),3,1) - DATE (1900,1,7),7) ,
0,7,MOD(DATE ( YEAR (DATEVALUE(i360__Start__c)),3,1) - DATE (1900,1,7),7))
)
&&
DATEVALUE(i360__Start__c) <
DATE ( YEAR (DATEVALUE(i360__Start__c)),11,1)
+
(
7-
CASE( MOD(DATE ( YEAR (DATEVALUE(i360__Start__c)),11,1) - DATE (1900,1,7),7) ,
0,7,MOD(DATE ( YEAR (DATEVALUE(i360__Start__c)),11,1) - DATE (1900,1,7),7))
),
IF(
OR(
VALUE( MID( TEXT( i360__Start__c - (4/24)), 12, 2 ) ) = 0,
VALUE( MID( TEXT(i360__Start__c - (4/24)), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT(i360__Start__c - (4/24)), 12, 2 ) )
-
IF(
VALUE( MID( TEXT(i360__Start__c - (4/24)), 12, 2 ) ) < 12,
0,
12
)
)
)&
":" &
MID( TEXT( i360__Start__c - (4/24)), 15, 2 )
& " " &
IF(
VALUE( MID( TEXT( i360__Start__c - (4/24)), 12, 2 ) ) < 12,
"AM",
"PM"
),
IF(
OR(
VALUE( MID( TEXT( i360__Start__c - (5/24)), 12, 2 ) ) = 0,
VALUE( MID( TEXT(i360__Start__c - (5/24)), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT(i360__Start__c - (5/24)), 12, 2 ) )
-
IF(
VALUE( MID( TEXT(i360__Start__c - (5/24)), 12, 2 ) ) < 12,
0,
12
)
)
)&
":" &
MID( TEXT( i360__Start__c - (5/24)), 15, 2 )
& " " &
IF(
VALUE( MID( TEXT( i360__Start__c - (5/24)), 12, 2 ) ) < 12,
"AM",
"PM"
))
IF ( DATEVALUE(i360__Start__c) >=
DATE ( YEAR (DATEVALUE(i360__Start__c)),3,1)
+
(
14-
CASE( MOD(DATE ( YEAR (DATEVALUE(i360__Start__c)),3,1) - DATE (1900,1,7),7) ,
0,7,MOD(DATE ( YEAR (DATEVALUE(i360__Start__c)),3,1) - DATE (1900,1,7),7))
)
&&
DATEVALUE(i360__Start__c) <
DATE ( YEAR (DATEVALUE(i360__Start__c)),11,1)
+
(
7-
CASE( MOD(DATE ( YEAR (DATEVALUE(i360__Start__c)),11,1) - DATE (1900,1,7),7) ,
0,7,MOD(DATE ( YEAR (DATEVALUE(i360__Start__c)),11,1) - DATE (1900,1,7),7))
),
IF(
OR(
VALUE( MID( TEXT( i360__Start__c -(5/24)), 12, 2 ) ) = 0,
VALUE( MID( TEXT(i360__Start__c - (5/24)), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT(i360__Start__c - (5/24)), 12, 2 ) )
-
IF(
VALUE( MID( TEXT(i360__Start__c - (5/24)), 12, 2 ) ) < 12,
0,
12
)
)
)&
":" &
MID( TEXT( i360__Start__c - (5/24)), 15, 2 )
& " " &
IF(
VALUE( MID( TEXT( i360__Start__c - (5/24)), 12, 2 ) ) < 12,
"AM",
"PM"
),
IF(
OR(
VALUE( MID( TEXT( i360__Start__c - (6/24)), 12, 2 ) ) = 0,
VALUE( MID( TEXT(i360__Start__c - (6/24)), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT(i360__Start__c - (6/24)), 12, 2 ) )
-
IF(
VALUE( MID( TEXT(i360__Start__c - (6/24)), 12, 2 ) ) < 12,
0,
12
)
)
)&
":" &
MID( TEXT( i360__Start__c - (6/24)), 15, 2 )
& " " &
IF(
VALUE( MID( TEXT( i360__Start__c - (6/24)), 12, 2 ) ) < 12,
"AM",
"PM"
))
Troubleshooting Formula field configurations
Occasionally, clients want to give a Homeowner an Arrival Time Window, rather than a specific Start Time. For this, we will create a Time formula that is three hours different from the original formula:
For Eastern Time, when you're 4 timezones removed from UTC, calculate the decimal value of 4 hours out of a 24-hour period. Some customers have these calculations using decimal format, rather than a fraction:
When you are 4 Hours removed from GMT during Standard time, the calculation is
4 /24 = 0.1666666666666667
When you are 5 Hours removed from GMT (during Daylight Savings time) the calculation is
5/24 = 0.2083333333333333
NOTE: The above example is for Eastern Time Zone
Considerations
You can replace the older formula with the appropriate Time Zone formula (from above). The formulas above in Resources do account for the change back and forth between Daylight Savings Time and Standard Time.
An Admin may have included Help Text allowing future Admins to see the reason behind the calculation, and when Daylight Savings Time was changed, how the calculation should differ. This example is for Pacific Time Zone:
Note: if you keep the older formula and do not Edit the Formula field when Daylight Savings Time changes, the Start Time Formula will be off by 1 Hour: