2

I am working on a formula based on a date/time value to extract the time for the field. This field is based on the Pacific Time Zone and as is appears as

04/04/2016 9:00 PM

I set up this formula below

LEFT(RIGHT(text( HOC__End_Date_Time__c ),9),7)

However the return result is based on GMT and returns based on a 24-hour clock, is there a way to adjust so it can simply display as 9:00 PM rather than as 24:00.

Hope it helps.

3

All date-time values are stored in the database in GMT. To return the time as a string in “HH:MM:SS A/PM” format, use the following formula where TZoffset is the offset (in hours) from GMT:

IF(
  OR(
    VALUE( MID( TEXT( ***date/time*** - TZoffset ), 12, 2 ) ) = 0,
    VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 12
  ),
  "12",
  TEXT( VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) 
   -
   IF( 
     VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12, 
     0, 
     12 
   ) 
  )
)
& ":" &
MID( TEXT( date/time - TZoffset ), 15, 2 )
& ":" &
MID( TEXT( date/time - TZoffset ), 18, 2 )
& " " &
IF(
  VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12,
  "AM",
  "PM"
)
  • How to define TZoffset for users in different zones? What about Daylight Saving Time? – Ilya Lepesh May 1 '16 at 1:08
  • @IlyaLepesh. The question was only about users in Pacific Time. While $User.Profile contains info about a user's time zone, it can't be accessed in a formula. Issues related to DST time and time zones with these formulas are well known & have been discussed in countless posts over the last several years. – crmprogdev May 2 '16 at 13:00
0

Salesforce Help shows: There’s no way to determine a user’s time zone in a formula.

Custom page could be created, where you will display only your Date/Time field. Execute JS while field is rendering.

Create new Page with name 'HOC', add it to your object layout. In page properties define Height=15 and make Show Label checked.

VF:

<apex:page standardController="Opportunity" id="pg">
    <apex:outputField value="{!opportunity.createddate}" id="st">
        <script type="text/javascript">
        (function(){
            var fd = document.getElementById("pg:st");
            fd.innerHTML = field.innerHTML.substring(field.innerHTML.indexOf(' '));
        })();
        </script>
    </apex:outputField>
</apex:page>

Result:

extracted time

Pros: This workaround will work for different time zones. Cons: It couldn't be referenced in child object, reports etc. as formula could. It needs some time to be loaded. It has small right alignment.

  • I like the idea of a visualforce page to reference time, but the key thing I am aiming for is the ability to reference the field in an email template or other external resources. The formula provided by crmprogdev works nicely, I just simply divided the offset which in my case is 7 by 24 and subtract to get the result needed. – Medhanie Habte May 4 '16 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.