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

A common complaint issue 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 a 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 merge fields in Documents and Email Templates to generate the correct time. 

Note: You WILL need to be update these fields to calculate the time correctly when DST (Daylight Savings Time) occurs. 

Start Time Formula 

• Navigate to Setup > Objects > Project Activity > Create New Custom Field
• Field Type: Formula
• Formula Return Type: Text
• Field Label: Start Time

Note: Replace the bolded/underlined section based on org time zone. More information on how to calculate the correct decimal value: Date or Time calculations in Formula fields, Workflow field updates and validation rules

IF(
OR(
VALUE( MID( TEXT( i360__Start__c - 0.20833333), 12, 2 ) ) = 0,
VALUE( MID( TEXT(i360__Start__c - 0.20833333), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT(i360__Start__c - 0.20833333), 12, 2 ) )
-
IF(
VALUE( MID( TEXT(i360__Start__c - 0.20833333), 12, 2 ) ) < 12,
0,
12
)
)
)&
":" &
MID( TEXT( i360__Start__c - 0.20833333), 15, 2 )
& " " &
IF(
VALUE( MID( TEXT( i360__Start__c - 0.20833333), 12, 2 ) ) < 12,
"AM",
"PM"
)

Note:

Adjustments need to be made to this Formula when Daylight Savings time changes. For example: Pacific Daylight Time is 7 Hours different from UTC. Pacific Standard Time is 8 Hours different. When you're 7 timezones removed from UTC, calculate the decimal value of 7 hours out of a 24-hour period:
7 * 60 minutes = 420 minutes
420/1440 minutes in a 24-hour period = 0.2916666666666667
When you are 8 Hours different use:
8 * 60 minutes = 480 minutes
480/1440 minutes in a 24-hour period = 0.3333333333333333

NOTE:This Example is for Pacific Time Zone

End Time Formula 

• Navigate to Setup > Objects > Project Activity > Create New Custom Field
• Field Type: Formula
• Formula Return Type: Text
• Field Label: End Time

Note: Replace the bolded/underlined section based on org time zone. More information on how to calculate the correct decimal value: Date or Time calculations in Formula fields, Workflow field updates and validation rules

IF(
OR(
VALUE( MID( TEXT( i360__End__c - 0.20833333), 12, 2 ) ) = 0,
VALUE( MID( TEXT(i360__End__c - 0.20833333), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT(i360__End__c - 0.20833333), 12, 2 ) )
-
IF(
VALUE( MID( TEXT(i360__End__c - 0.20833333), 12, 2 ) ) < 12,
0,
12
)
)
)&
":" &
MID( TEXT(i360__End__c - 0.20833333), 15, 2 )
& " " &
IF(
VALUE( MID( TEXT( i360__End__c - 0.20833333), 12, 2 ) ) < 12,
"AM",
"PM"
)

Customizations

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 Pacific Time, when you're 7 timezones removed from UTC, calculate the decimal value of 4 hours out of a 24-hour period:
4 * 60 minutes = 240 minutes
240/1440 minutes in a 24-hour period = 0.1666666666666667
When you are 8 Hours different calculate 5 Hour:
5 * 60 minutes = 300 minutes
300/1440 minutes in a 24-hour period = 0.2083333333333333

NOTE: This Example is for Pacific Time Zone

Considerations

It may be helpful to include Help Text allowing future Admins to see the reason behind the calculation, and when Daylight Savings Time changes, how does the calculation differ: 

Note: if you do not Edit the Formula field when Daylight Savings Time changes, the Start Time Formula will be off by 1 Hour: