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"
)

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"
)