3

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.

Improve this question

2 Answers 2

Reset to default
5

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"
)
Improve this answer
2
  • How to define TZoffset for users in different zones? What about Daylight Saving Time?
    – Ilya Lepesh
    May 1, 2016 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, 2016 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.

Improve this answer
1
  • 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, 2016 at 18:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .