Custom Formula Field: Generate Start and End Times in Correct Time Zone

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. 

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. 

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 -0.29166667), 12, 2 ) ) = 0,
VALUE( MID( TEXT(i360__Start__c - 0.29166667), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT(i360__Start__c - 0.29166667), 12, 2 ) )
-
IF(
VALUE( MID( TEXT(i360__Start__c - 0.29166667), 12, 2 ) ) < 12,
0,
12
)
)
)&
":" &
MID( TEXT( i360__Start__c - 0.29166667), 15, 2 )
& " " &
IF(
VALUE( MID( TEXT( i360__Start__c - 0.29166667), 12, 2 ) ) < 12,
"AM",
"PM"
),
IF(
OR(
VALUE( MID( TEXT( i360__Start__c - 0.33333333), 12, 2 ) ) = 0,
VALUE( MID( TEXT(i360__Start__c - 0.33333333), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT(i360__Start__c - 0.33333333), 12, 2 ) )
-
IF(
VALUE( MID( TEXT(i360__Start__c - 0.33333333), 12, 2 ) ) < 12,
0,
12
)
)
)&
":" &
MID( TEXT( i360__Start__c - 0.33333333), 15, 2 )
& " " &
IF(
VALUE( MID( TEXT( i360__Start__c - 0.33333333), 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 -0.29166667), 12, 2 ) ) = 0,
VALUE( MID( TEXT(i360__End__c - 0.29166667), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT(i360__End__c - 0.29166667), 12, 2 ) )
-
IF(
VALUE( MID( TEXT(i360__End__c - 0.29166667), 12, 2 ) ) < 12,
0,
12
)
)
)&
":" &
MID( TEXT( i360__End__c - 0.29166667), 15, 2 )
& " " &
IF(
VALUE( MID( TEXT( i360__End__c - 0.29166667), 12, 2 ) ) < 12,
"AM",
"PM"
),
IF(
OR(
VALUE( MID( TEXT( i360__End__c - 0.33333333), 12, 2 ) ) = 0,
VALUE( MID( TEXT(i360__End__c - 0.33333333), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT(i360__End__c - 0.33333333), 12, 2 ) )
-
IF(
VALUE( MID( TEXT(i360__End__c - 0.33333333), 12, 2 ) ) < 12,
0,
12
)
)
)&
":" &
MID( TEXT( i360__End__c - 0.33333333), 15, 2 )
& " " &
IF(
VALUE( MID( TEXT( i360__End__c - 0.33333333), 12, 2 ) ) < 12,
"AM",
"PM"
))

Resources 

Below you will find the Start formula formatting for Eastern Time and Central Time Zones. 

Eastern Time Zone:

Troubleshooting older 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:

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: